[20220603]测试quiz night(补充).txt

[20220603]测试quiz night(补充).txt --//晚上https://jonathanlewis.wordpress.com/2022/05/20/quiz-night-37/的补充,作者给出了答案. Answer 答案 It's fairly common knowledge that Oracle includes a "length byte" in the estimates for average column length and average row length. It's also fairly well known that "trailing nulls take no space" in a row so don't need a length byte. There are a couple of "less common knowledge" details to add, though: 众所周知，Oracle在对平均列长度和平均行长度的估计中包含了一个字节长度。众所周知，后面的空值不需要一行中的空格，因此不需要 长度字节。有一些不太常见的知识需要添加的细节： any column that is null in every row gets a zero for the avg_col_len even if it isn't a "trailing null". 每行中为空的任何列对于avg_col_len都为零，即使它不是"后面的null"。 but point 1 doesn't apply to date columns that are always null, they get a 1 for the avg_col_len even the column is a "trailing null".. The same is true for the various timestamp and interval types. 但是点1不适用于总是空的日期列，它们为avg_col_len得到一个1，即使列是一个"后面的null"对于各种时间戳和时间间隔类型也是如此。 for columns that hold at least one value the avg_col_len is the average over all rows of the actual space used by that column's data, rounded up, plus 1 for the length byte. 对于那些至少包含一个值的列，avg_col_len是由该列的数据所使用的实际空间的所有行的平均值，四舍五入，加上1表示长度字节。 the avg_row_len is not the sum(avg_col_len) it is based on the average of the summed column lengths for each row, plus the count of the length bytes recorded. avg_row_len不是总和(avg_col_len)，它是基于每行的总和列长度的平均值，加上记录的长度字节的计数。 User defined type, LOBs, varray types etc. introduce all sorts of other strange effects. (But that's probably "more common" knowledge.) 用户定义的类型、lob、各种类型等。引入各种其他奇怪的效果。(但这很可能是更常见的知识。) So what does that mean in my example where there's a declared not null column near the end of the row, with two trailing columns and with every column except the first and the non-null column set to null for every single row in the table? The easy option is to create the model and show you the results of querying user_tab_cols. 那么，在我的例子中，在行的末尾有一个声明的非空列，有两个尾列，除了表中一行的第一列和非空列设置为空，这意味着什么呢？ 最简单的选择是创建模型，并向您显示查询user_tab_cols的结果。 --//实际上最简单就是查询user_tab_cols相关视图. break on report compute sum of avg_col_len on report select column_name, data_type, avg_col_len from user_tab_cols where table_name = 'INTERR_SKUPLANNPARAM' and avg_col_len != 0 order by column_id; COLUMN_NAME          DATA_TYPE            AVG_COL_LEN -------------------- -------------------- ----------- ATPDUR               NUMBER                         2 FIRSTREPLENDATE      DATE                           1 LASTFRZSTART         DATE                           1 LASTPLANSTART        DATE                           1 DRPTIMEFENCEDATE     DATE                           1 MPSTIMEFENCEDATE     DATE                           1 EXPDATE              DATE                           1 PRODSTARTDATE        DATE                           1 PRODSTOPDATE         DATE                           1 INTEGRATION_STAMP    DATE                           1 INTEGRATION_JOBID    VARCHAR2                       8 ERROR_STAMP          DATE                           1                                           ----------- sum                                                20 12 rows selected. My query of user_tab_cols orders by column_id, technically it should order by segment_column_id to show the physical ordering in the data segment to allow for all the strange effects you can get in more complex scenarios, but in this very simple case the two values are the same. 我对column_id的user_tab_cols订单的查询，从技术上讲，它应该通过segment_column_id来显示数据段中的物理顺序，以允许在更复杂 的场景中可以获得的所有奇怪效果，但在这个非常简单的情况下，这两个值是相同的。 As you can see, every date type (including the trailing error_stamp) has an avg_col_len of 1, even though all the dates are null in every row. Column atdpur has avg_col_len = 2, which is 1 byte for storing zero plus a length byte and integration_job_id has avg_col_len = 8, which is 7 bytes for storing 'INT_JOB' plus a length byte. 正如您所看到的，每个日期类型(包括后面的error_stamp)的avg_col_len都为1，尽管每行中的所有日期都为空。列atdpur有 avg_col_len=2，它是1个字节用于存储零加上一个长度字节，而integration_job_id有avg_col_len=8，它是7个字节用于存储"INT_JOB" 加上一个长度字节。 In this case where every single row is identical there are no rounding effects due to the calculation of average column length (the column data stored is the same in every row) so the avg_row_len = sum(avg_col_len). 在这种情况下，每一行都是相同的，由于计算平均列长度(存储的列数据是相同的)，没有舍入效应，所以avg_row_len=sum(avg_col_len)。 --//作者还给出了一些测试: update interr_skuplannparam set         shrinkagefactor = 1234567890,         item            = 'xxx',         expdate         = sysdate where         rownum = 1 / commit; execute dbms_stats.gather_table_stats(user,'interr_skuplannparam') SCOTT@test01p> select  avg_row_len from    user_tables where   table_name = 'INTERR_SKUPLANNPARAM' ; AVG_ROW_LEN -----------          22 --//仅仅改动1条记录,导致AVG_ROW_LEN=22. select column_name, data_type, avg_col_len from user_tab_cols where table_name = 'INTERR_SKUPLANNPARAM' and avg_col_len != 0 order by column_id;         COLUMN_NAME          DATA_TYPE            AVG_COL_LEN -------------------- -------------------- ----------- ATPDUR               NUMBER                         2 FIRSTREPLENDATE      DATE                           1 LASTFRZSTART         DATE                           1 LASTPLANSTART        DATE                           1 DRPTIMEFENCEDATE     DATE                           1 MPSTIMEFENCEDATE     DATE                           1 SHRINKAGEFACTOR      NUMBER                         2 ITEM                 VARCHAR2                       2 EXPDATE              DATE                           2 PRODSTARTDATE        DATE                           1 PRODSTOPDATE         DATE                           1 INTEGRATION_STAMP    DATE                           1 INTEGRATION_JOBID    VARCHAR2                       8 ERROR_STAMP          DATE                           1                                           ----------- sum                                                25 14 rows selected. --//也就是不能简单累加作为AVG_ROW_LEN. The total of the avg_col_len has gone up from 20 to 25 – this is two bytes each for the shrinkage_factor and item columns (a tiny average for the stored data, plus 1 for a length byte), and one extra byte for the expdate column (a tiny average for the stored data). All three values rounded up from "1 and a bit" to 2. avg_col_len的总数从20上升到25——shrinkage_factor和项目列各两个字节(存储数据的平均值很小，长度字节加1)，扩展列有一个额外 字节(存储数据的很小平均值)。所有三个值都从1和位入到2。 The avg_row_len, however, has gone up by only 2 – which I am going to assume is the two newlength bytes, ,and with no allowance for the impact of the one row in 10,000 that is now a few bytes longer. It looks as if the rounding rules for the row length may be different from the rounding (up) rules for the column length. 然而，avg_row_len只上升了2——我假设这是两个新长度的字节，并且不考虑到10,000行的影响，现在增加了几个字节。似乎行长度的舍 入规则可能与列长度的舍入(向上)规则不同。