[20210421]12c以上版本增加字段与缺省值.txt

[20210421]12c以上版本增加字段与缺省值.txt --//昨天在优化sql语句时，发现过滤条件有点奇怪，才发现是12c以上增加字段与缺省值导致的情况。 --//今天测试各种增加字段的情况。 1.环境： TTT@192.168.2.7:1521/orcl> @ prxx ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 18.0.0.0.0 BANNER                        : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 BANNER_LEGACY                 : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. 2.测试: create table t (a number); insert into t select rownum from dual connect by rownum <=3; commit; --//分析略。 alter table t add ( b number default 1 ); alter table t add ( c number default 2 not null); alter table t add ( d number ); alter table t add ( e number default on null 4); --//注：我所知道就这么4种，也许还有其它方式。 alter table t add ( f number default 5 ); TTT@192.168.2.7:1521/orcl> @desc t    Name  Null?    Type    ----- -------- -------- 1  A              NUMBER 2  B              NUMBER 3  C     NOT NULL NUMBER 4  D              NUMBER 5  E     NOT NULL NUMBER 6  F              NUMBER TTT@192.168.2.7:1521/orcl> select OBJECT_ID,DATA_OBJECT_ID from dba_objects where object_name='T';  OBJECT_ID DATA_OBJECT_ID ---------- --------------     252156         252156 TTT@192.168.2.7:1521/orcl> select TABOBJ#,COLNUM,BINARYDEFVAL,GUARD_ID from sys.ecol$ a;      TABOBJ#       COLNUM BINARYDEFVAL                       GUARD_ID ------------ ------------ ------------------------------ ------------       252156            3 C102                                      0       252156            6 C105       252156            4 C103       252156            7 C106                                      1 --//guard_id表示顺序。 SELECT obj#         ,col#         ,segcol#         ,INTCOL#         ,name         ,default$         ,null$         ,type#         ,PROPERTY     FROM sys.col$    WHERE obj# IN (SELECT object_id                     FROM dba_objects                    WHERE owner = 'TTT' AND object_name = 'T') ORDER BY INTCOL#;   OBJ# COL# SEGCOL# INTCOL# NAME        DEFAULT$ NULL$ TYPE#     PROPERTY ------ ---- ------- ------- ----------- -------- ----- ----- ------------ 252156    1       1       1 A                        0     2            0 252156    0       2       2 SYS_NC00002              0    23 549755814176 252156    2       3       3 B           1            0     2   1073741824 252156    3       4       4 C           2            1     2   1073741824 252156    4       5       5 D                        0     2            0 252156    5       6       6 E           4            1     2  69793218560 252156    6       7       7 F           5            0     2   1073741824 7 rows selected. --//顺便说明COL#,SEGCOL#,INTCOL#关系。 --// SEGCOL# 表示在数据段的存储顺序，按照 A,SYS_NC00002$,B,C,D,E,F. --// INTCOL# 表示定义表时的定义字段的顺序，这里与SEGCOL#顺序一致，有时候会出现不一致的情况，比如IOT表，其主键在数据块存储中在 --// 最前面。 --// col# 不等于0的列显示。换一句话将0表示隐含列。按照select * from t的字段显示顺序. --//可以参考 http://www.laoxiong.net/dict_col_segcol_intcol.html --//12c有一个新特性隐藏列，这样就可以改变显示顺序，大家可以自行探究。 --//似乎以下两个是很类似,PROPERTY不同。e字段可以插入null，实际上进入插入的是4. --//69793218560=1000001000000000000000000000000000000(二进制) --//1073741824 =      1000000000000000000000000000000(二进制) alter table t add ( c number default 2 not null); alter table t add ( e number default on null 4); TTT@192.168.2.7:1521/orcl> insert into t (a ) values (4); 1 row created. insert into t (a,e ) values (5,NULL); TTT@192.168.2.7:1521/orcl> commit ; Commit complete. TTT@192.168.2.7:1521/orcl> select t.*,SYS_NC00002$ c10  from t ;  A  B  C  D  E  F C10 -- -- -- -- -- -- ----  1  1  2     4  5  2  1  2     4  5  3  1  2     4  5  4  1  2     4  5 03  5  1  2     4  5 03 --//A=5对应的E=4,即使插入是NULL. TTT@192.168.2.7:1521/orcl> @ dpc '' advanced ... Column Projection Information (identified by operation id): -----------------------------------------------------------    1 - "T"."A"[NUMBER,22],        "SYS_NC00002$"[RAW,126],        DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00002$",0)),NULL,NVL("T"."B",1),'0',NVL("T"."B",1),'1',"T"."B")[22],        NVL("T"."C",2)[22],        "T"."D"[NUMBER,22],        NVL("T"."E",4)[22],        DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00002$",1)),NULL,NVL("T"."F",5),'0',NVL("T"."F",5),'1',"T"."F")[22] 3.看看块的转储情况： TTT@192.168.2.7:1521/orcl> select rowid,t.*,SYS_NC00002$ c10  from t ; ROWID               A  B  C  D  E  F C10 ------------------ -- -- -- -- -- -- ---- AAA9j8AAMAAAACHAAA  1  1  2     4  5 AAA9j8AAMAAAACHAAB  2  1  2     4  5 AAA9j8AAMAAAACHAAC  3  1  2     4  5 AAA9j8AAMAAAACHAAE  4  1  2     4  5 03 AAA9j8AAMAAAACHAAF  5  1  2     4  5 03 TTT@192.168.2.7:1521/orcl> @ rowid AAA9j8AAMAAAACHAAA       OBJECT         FILE        BLOCK          ROW ROWID_DBA            DBA                  TEXT ------------ ------------ ------------ ------------ -------------------- -------------------- ----------------------------------------       252156           12          135            0  0x3000087           12,135               alter system dump datafile 12 block 135 TTT@192.168.2.7:1521/orcl> @ rowid_abs AAA9j8AAMAAAACHAAF TTT T     OBJECT   FILE_ABS       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT ---------- ---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------     252156         41         12        135          5  0x3000087           12,135               alter system dump datafile 41 block 135 --//说明使用cdb数据库,相对文件号与绝对文件号不同.     TTT@192.168.2.7:1521/orcl> alter system dump datafile 41 block 135; System altered. --//检查转储： Block header dump:  0x03000087  Object id on Block? Y  seg/obj: 0x3d8fc  csc:  0x0000000058dc696b  itc: 2  flg: E  typ: 1 - DATA      brn: 0  bdba: 0x3000080 ver: 0x01 opc: 0      inc: 0  exflg: 0  Itl           Xid                  Uba         Flag  Lck        Scn/Fsc 0x01   0x0007.005.00012658  0x02400930.3209.19  --U-    2  fsc 0x0000.58dc6a4b 0x02   0x000a.001.0001bf15  0x02401532.6f2b.3b  --U-    1  fsc 0x0000.58dc6f3b bdba: 0x03000087 data_block_dump,data header at 0x7f4851c0f064 =============== tsiz: 0x1f98 hsiz: 0x1e pbl: 0x7f4851c0f064      76543210 flag=-------- ntab=1 nrow=6 frre=-1 fsbo=0x1e fseo=0x1f4a avsp=0x1f33 tosp=0x1f35 0xe:pti[0]      nrow=6  offs=0 0x12:pri[0]     offs=0x1f86 0x14:pri[1]     offs=0x1f8c 0x16:pri[2]     offs=0x1f92 0x18:pri[3]     offs=0x1f74 0x1a:pri[4]     offs=0x1f5f 0x1c:pri[5]     offs=0x1f4a block_row_dump: tab 0, row 0, @0x1f86 tl: 6 fb: --H-FL-- lb: 0x0  cc: 1 col  0: [ 2]  c1 02 tab 0, row 1, @0x1f8c tl: 6 fb: --H-FL-- lb: 0x0  cc: 1 col  0: [ 2]  c1 03 tab 0, row 2, @0x1f92 tl: 6 fb: --H-FL-- lb: 0x0  cc: 1 col  0: [ 2]  c1 04 tab 0, row 3, @0x1f74 tl: 2 fb: --HDFL-- lb: 0x1 tab 0, row 4, @0x1f5f tl: 21 fb: --H-FL-- lb: 0x1  cc: 7 col  0: [ 2]  c1 05 col  1: [ 1]  03 col  2: [ 2]  c1 02 col  3: [ 2]  c1 03 col  4: *NULL* col  5: [ 2]  c1 05 col  6: [ 2]  c1 06 tab 0, row 5, @0x1f4a tl: 21 fb: --H-FL-- lb: 0x2  cc: 7 col  0: [ 2]  c1 06 col  1: [ 1]  03 col  2: [ 2]  c1 02 col  3: [ 2]  c1 03 col  4: *NULL* col  5: [ 2]  c1 05 col  6: [ 2]  c1 06 end_of_block_dump --//后面插入的记录都写盘了。 --//另外我在想是否开发应该了解这些增加字段的优缺点。