ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > [20210421]12c以上版本增加字段与缺省值.txt

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

原创 Oracle 作者：lfree 时间：2021-04-22 08:57:46 1 删除 编辑

[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
--//后面插入的记录都写盘了。

--//另外我在想是否开发应该了解这些增加字段的优缺点。

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2769203/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： [20210420]ORA-00904 REF invalid identifier 19c dba_obj_audit_opts.txt
下一篇： [20210421]分析会话占用的共享内存段2.txt
请登录后发表评论 登录
全部评论
lfree
专家姓名：张文胜
熟悉oracle相关技术，擅长sql优化，rman备份与恢复，熟悉linux shell编程。

注册时间：2008-01-03

  • 博文量
    4014
  • 访问量
    7991521

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us 编辑准则

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593