[20220603]測試quiz night(補充).txt

[20220603]測試quiz night(補充).txt

--//晚上的補充,作者給出了答案.

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行的影響，現在增加了幾個位元組。似乎行長度的舍
入規則可能與列長度的舍入(向上)規則不同。