[20220531]测试quiz night.txt

[20220531]测试quiz night.txt --//https://jonathanlewis.wordpress.com/2022/05/20/quiz-night-37/,自己重复测试看看。 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，为什么不理解以前也没有注意这个细节。 --//期待作者的解析。