[20220531]測試quiz night.txt

--//自己重複測試看看。

1.環境：
SCOTT@book> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.測試：
/* Formatted on 2022/5/31 10:41:31 (QP5 v5.269.14213.34769) */
CREATE TABLE interr_skuplannparam
(
   atpdur                     NUMBER (38)
,depdmdopt                  NUMBER (38)
,externalskusw              NUMBER (1)
,firstreplendate            DATE
,lastfrzstart               DATE
,lastplanstart              DATE
,plandur                    NUMBER (38)
,planleadtime               NUMBER (38)
,planleadtimerule           NUMBER (38)
,planshipfrzdur             NUMBER (38)
,restrictdur                NUMBER (38)
,allocbatchsw               NUMBER (1)
,cmpfirmdur                 NUMBER (38)
,custservicelevel           FLOAT (126)
,maxchangefactor            FLOAT (126)
,mfgleadtime                NUMBER (38)
,recschedrcptsdur           NUMBER (38)
,cpppriority                NUMBER (38)
,cpplocksw                  NUMBER (1)
,criticalmaterialsw         NUMBER (1)
,aggexcesssupplyrule        NUMBER (38)
,aggundersupplyrule         NUMBER (38)
,bufferleadtime             NUMBER (38)
,maxoh                      FLOAT (126)
,maxcovdur                  NUMBER (38)
,drpcovdur                  NUMBER (38)
,drpfrzdur                  NUMBER (38)
,drprule                    NUMBER (38)
,drptimefencedate           DATE
,drptimefencedur            NUMBER (38)
,incdrpqty                  FLOAT (126)
,mindrpqty                  FLOAT (126)
,mpscovdur                  NUMBER (38)
,mfgfrzdur                  NUMBER (38)
,mpsrule                    NUMBER (38)
,mpstimefencedate           DATE
,mpstimefencedur            NUMBER (38)
,incmpsqty                  FLOAT (126)
,minmpsqty                  FLOAT (126)
,shrinkagefactor            NUMBER (38)
,item                       VARCHAR2 (50 CHAR)
,loc                        VARCHAR2 (50 CHAR)
,expdate                    DATE
,atprule                    NUMBER (38)
,prodcal                    VARCHAR2 (50 CHAR)
,prodstartdate              DATE
,prodstopdate               DATE
,orderingcost               FLOAT (126)
,holdingcost                FLOAT (126)
,eoq                        FLOAT (126)
,ff_trigger_control         NUMBER (38)
,workingcal                 VARCHAR2 (50 CHAR)
,lookaheaddur               NUMBER
,orderpointrule             NUMBER
,orderskudetailsw           NUMBER (1)
,supsdmindmdcovdur          NUMBER (38)
,orderpointminrule          NUMBER (38)
,orderpointminqty           FLOAT (126)
,orderpointmindur           NUMBER (38)
,orderuptolevelmaxrule      NUMBER (38)
,orderuptolevelmaxqty       FLOAT (126)
,orderuptolevelmaxdur       NUMBER (38)
,aggskurule                 NUMBER (38)
,fwdbuymaxdur               NUMBER (38)
,costuom                    NUMBER (38)
,cumleadtimedur             NUMBER (38)
,cumleadtimeadjdur          NUMBER (38)
,cumleadtimerule            NUMBER (38)
,roundingfactor             FLOAT (126)
,limitplanarrivpublishsw    NUMBER (1)
,limitplanarrivpublishdur   NUMBER
,maxohrule                  NUMBER (1)
,integration_stamp          DATE
,integration_jobid          VARCHAR2 (32 CHAR) NOT NULL
,error_str                  VARCHAR2 (2000 CHAR)
,error_stamp                DATE
);

SCOTT@book> alter table interr_skuplannparam modify integration_jobid default 'INT_JOB';
Table altered.

--//truncate table interr_skuplannparam;

insert into interr_skuplannparam (atpdur)
select   0
from     all_objects
where    rownum <= 10000
/

commit;

execute dbms_stats.gather_table_stats(user,'interr_skuplannparam')

SCOTT@book> SELECT AVG_ROW_LEN FROM USER_TABLES WHERE TABLE_NAME='INTERR_SKUPLANNPARAM';
AVG_ROW_LEN
-----------
         20

--//怎麼是20呢，我在測試前做了估算開始因為長度指示器佔1，數字0編碼0x80,佔1個位元組，72個NULL，integration_jobid預設值是
--//INT_JOB佔7，加上長度指示器1.
--//2+72+1+7 = 82應該是82。後面的null值oracle不儲存。

--//一位讀者的解析：
The result of the last query cannot be determined with the given information – unless interr_skuplannparam is the only
table in the current schema. If it is the only table, the expected result is 1 (row contains not null values) + 1 (size
of Oracle's representation of 0) + (7+1) (length of the not-null column in bytes plus one byte to store the string's
length) + 10*1 (because there are 10 date columns) = 20.

不能用給定的資訊來確定最後一個查詢的結果——除非interr_skuplannparam是當前模式中唯一的表。如果是唯一的表，預期結果是1(行
不包含空值)+1(Oracle表示0的大小)+(7+1)(位元組的非空列長度加上一個位元組儲存字串的長度)+10*1(因為有10個日期列)=20。

--//按照讀者的演算法僅僅考慮日期型別的NULL,為什麼呢?
--//透過bbed檢視分析：

SCOTT@book> select rowid from interr_skuplannparam where rownum=1;
ROWID
------------------
AABRWQAAEAAAAkbAAA

SCOTT@book> @ rowid AABRWQAAEAAAAkbAAA
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
    333200          4       2331          0 0x100091B           4,2331               alter system dump datafile 4 block 2331

BBED> set width 160
        WIDTH           160

BBED> set dba 4,2331
        DBA             0x0100091b (16779547 4,2331)

BBED> x /rnxxxx *kdbr[0]
rowdata[3740]                               @4873
-------------
flag@4873: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@4874: 0x01
cols@4875:   74

col    0[1] @4876: 0
col    1[0] @4878: *NULL*
col    2[0] @4879: *NULL*
col    3[0] @4880: *NULL*
col    4[0] @4881: *NULL*
col    5[0] @4882: *NULL*
col    6[0] @4883: *NULL*
col    7[0] @4884: *NULL*
col    8[0] @4885: *NULL*
col    9[0] @4886: *NULL*
col   10[0] @4887: *NULL*
col   11[0] @4888: *NULL*
col   12[0] @4889: *NULL*
col   13[0] @4890: *NULL*
col   14[0] @4891: *NULL*
col   15[0] @4892: *NULL*
col   16[0] @4893: *NULL*
col   17[0] @4894: *NULL*
col   18[0] @4895: *NULL*
col   19[0] @4896: *NULL*
col   20[0] @4897: *NULL*
col   21[0] @4898: *NULL*
col   22[0] @4899: *NULL*
col   23[0] @4900: *NULL*
col   24[0] @4901: *NULL*
col   25[0] @4902: *NULL*
col   26[0] @4903: *NULL*
col   27[0] @4904: *NULL*
col   28[0] @4905: *NULL*
col   29[0] @4906: *NULL*
col   30[0] @4907: *NULL*
col   31[0] @4908: *NULL*
col   32[0] @4909: *NULL*
col   33[0] @4910: *NULL*
col   34[0] @4911: *NULL*
col   35[0] @4912: *NULL*
col   36[0] @4913: *NULL*
col   37[0] @4914: *NULL*
col   38[0] @4915: *NULL*
col   39[0] @4916: *NULL*
col   40[0] @4917: *NULL*
col   41[0] @4918: *NULL*
col   42[0] @4919: *NULL*
col   43[0] @4920: *NULL*
col   44[0] @4921: *NULL*
col   45[0] @4922: *NULL*
col   46[0] @4923: *NULL*
col   47[0] @4924: *NULL*
col   48[0] @4925: *NULL*
col   49[0] @4926: *NULL*
col   50[0] @4927: *NULL*
col   51[0] @4928: *NULL*
col   52[0] @4929: *NULL*
col   53[0] @4930: *NULL*
col   54[0] @4931: *NULL*
col   55[0] @4932: *NULL*
col   56[0] @4933: *NULL*
col   57[0] @4934: *NULL*
col   58[0] @4935: *NULL*
col   59[0] @4936: *NULL*
col   60[0] @4937: *NULL*
col   61[0] @4938: *NULL*
col   62[0] @4939: *NULL*
col   63[0] @4940: *NULL*
col   64[0] @4941: *NULL*
col   65[0] @4942: *NULL*
col   66[0] @4943: *NULL*
col   67[0] @4944: *NULL*
col   68[0] @4945: *NULL*
col   69[0] @4946: *NULL*
col   70[0] @4947: *NULL*
col   71[0] @4948: *NULL*
col   72[0] @4949: *NULL*
col   73[7] @4950: 0x49 0x4e 0x54 0x5f 0x4a 0x4f 0x42

BBED> p kdbr
sb2 kdbr[0]                                 @118      4773
sb2 kdbr[1]                                 @120      4858
...

--//4858-4773 = 85 , 一行佔85位元組。行頭佔3個位元組
flag@4873: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@4874: 0x01
cols@4875:   74
--//而按照前面的計算僅僅計算前面是date型別的NULL值的情況
--//1+1+10+7+1 = 20，為什麼不理解以前也沒有注意這個細節。
--//期待作者的解析。

[20220531]測試quiz night.txt

相關文章