[20140823]11g增加字段与缺省值.txt
--12c 当插入NULL时可以指定缺省值.不知道为什么设置这个特性,有点怪怪的.自己先测试11G增加字段带缺省值的情况.
--11G 增加字段带缺省值,可以很快完成,不需要update表.简单测试其内部机制:
1.建立测试环境:
SCOTT@test01p> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
create table t (id number);
insert into t values (1);
commit ;
alter table t add (name varchar2(10) default 'test');
alter session set events '10046 trace name context forever, level 12';
SCOTT@test01p> select * from t;
ID NAME
---------- --------------------
1 test
--这个是11G的新特性,name建立的缺省值并不保存在块内,这样修改表结构很快.除非新插入的信息例外.
--家里只有12c,应该不影响测试结论.
2.分析跟踪文件:
=====================
PARSING IN CURSOR #242380976 len=98 dep=1 uid=0 oct=3 lid=0 tim=5758817148 hv=1737785696 ad='7ff2826cf90' sqlid='7qybza5mt90b0'
select binaryDefVal, lengthb(binaryDefVal), guard_id from ecol$ where tabobj# = :1 and colnum = :2
END OF STMT
PARSE #242380976:c=0,e=196,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=4082558504,tim=5758817146
BINDS #242380976:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=150b1f68 bln=22 avl=04 flg=05
value=96075
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=150b1f80 bln=22 avl=02 flg=01
value=3
EXEC #242380976:c=0,e=550,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=4082558504,tim=5758818034
FETCH #242380976:c=0,e=104,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=4082558504,tim=5758818206
STAT #242380976 id=1 cnt=1 pid=0 pos=1 obj=151 op='TABLE ACCESS BY INDEX ROWID BATCHED ECOL$ (cr=2 pr=0 pw=0 time=91 us cost=1 size=2041 card=1)'
STAT #242380976 id=2 cnt=1 pid=1 pos=1 obj=154 op='INDEX RANGE SCAN ECOL_IX1 (cr=1 pr=0 pw=0 time=51 us cost=1 size=0 card=1)'
CLOSE #242380976:c=0,e=6,dep=1,type=3,tim=5758818501
=====================
SCOTT@test01p> select * from sys.ecol$ where tabobj# = 96075 and colnum = 3;
TABOBJ# COLNUM BINARYDEFVAL GUARD_ID
---------- ---------- -------------------- ----------
96075 3 74657374 0
SCOTT@test01p> select dump('test',16) from dual ;
DUMP('TEST',16)
-------------------------
Typ=96 Len=4: 74,65,73,74
--正好与字符"test"对上.
select obj#,col#,segcol#,name,default$,type# from sys.col$ where
obj# in ( select object_id from dba_objects where owner=user and object_name='T')
order by col#;
OBJ# COL# SEGCOL# NAME DEFAULT$ TYPE#
----- ----- -------- -------------------- ---------- ------
96075 0 2 SYS_NC00002$ 23
96075 1 1 ID 2
96075 2 3 NAME 'test' 1
--TYPE#=23 ,应该是raw类型.
--可以从这里发现,实际上建立了一个隐含列SYS_NC00002$,在数据块上的保存顺序(按照segcol#排列)是ID,SYS_NC00002$,NAME.
--这里的SYS_NC00002$中的2表示第2个字段.相当于把name字段建立在第3个字段.接着插入数据看看:
3.插入更多的数据,分析数据块信息:
insert into t values(2,'aaaa');
insert into t values(3,NULL);
commit ;
SCOTT@test01p> select rowid,id,SYS_NC00002$,name from t;
ROWID ID SYS_NC00002$ NAME
------------------ ---------- ------------------------------ -------
AAAXdLAAJAAAAmOAAA 1 test
AAAXdLAAJAAAAmOAAB 2 01 aaaa
AAAXdLAAJAAAAmOAAC 3 01
--从这个里面我们可以推出内部的运算:
当SYS_NC00002$='01'时name从数据块里面取值,如果为NULL,从这里取值
select * from sys.ecol$ where tabobj#= 96075 and colnum = 3;
SCOTT@test01p> @lookup_rowid AAAXdLAAJAAAAmOAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
96075 9 2446 0 9,2446 alter system dump datafile 9 block 2446
--做一个块转储看看.
*** 2014-08-23 21:46:49.392
Block header dump: 0x0240098e
Object id on Block? Y
seg/obj: 0x1774b csc: 0x00.72a554 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2400988 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.004.00001185 0x014029a9.0281.0f --U- 1 fsc 0x0000.0072a555
0x02 0x0004.004.000013ab 0x01402898.02d3.0f --U- 2 fsc 0x0000.0072ae7b
bdba: 0x0240098e
data_block_dump,data header at 0xe634864
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x0e634864
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f7d
avsp=0x1f61
tosp=0x1f61
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0x1f92
0x14:pri[1] offs=0x1f85
0x16:pri[2] offs=0x1f7d
block_row_dump:
tab 0, row 0, @0x1f92
tl: 6 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 2] c1 02
tab 0, row 1, @0x1f85
tl: 13 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 03
col 1: [ 1] 01
col 2: [ 4] 61 61 61 61
tab 0, row 2, @0x1f7d
tl: 8 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 04
col 1: [ 1] 01
end_of_block_dump
End dump data blocks tsn: 3 file#: 9 minblk 2446 maxblk 2446
SCOTT@test01p> select dump(1,16),dump(2.16) ,dump(3,16) from dual;
DUMP(1,16) DUMP(2.16) DUMP(3,16)
----------------- --------------------- -----------------
Typ=2 Len=2: c1,2 Typ=2 Len=3: 193,3,17 Typ=2 Len=2: c1,4
--可以看出这种方式添加字段很快,但是要付出一点点代价的,后续的插入,name非空时,要多付出2个字节的代价(说明01占用1个字节,另外
--还有前面的长度指示器),可以看到id=3的记录插入name='NULL',也要付出2个字节的代价.
4.再增加1个字段看看.
alter table t add (status number default 0);
SCOTT@test01p> select * from sys.ecol$ where tabobj#= 96075 ;
TABOBJ# COLNUM BINARYDEFVAL GUARD_ID
---------- ---------- -------------------- ----------
96075 3 74657374 0
96075 4 80 1
select obj#,col#,segcol#,name,default$,type# from sys.col$ where
obj# in ( select object_id from dba_objects where owner=user and object_name='T')
order by col#;
OBJ# COL# SEGCOL# NAME DEFAULT$ TYPE#
----- ----- -------- ------------- --------- ------
96075 0 2 SYS_NC00002$ 23
96075 1 1 ID 2
96075 2 3 NAME 'test' 1
96075 3 4 STATUS 0 2
--可以发现并没有在增加隐含列.再看看执行如下sql语句的结果:
SCOTT@test01p> select rowid,id,SYS_NC00002$,name,status from t;
ROWID ID SYS_NC00002$ NAME STATUS
------------------ ---------- ------------------------------ -------------------- ----------
AAAXdLAAJAAAAmOAAA 1 test 0
AAAXdLAAJAAAAmOAAB 2 01 aaaa 0
AAAXdLAAJAAAAmOAAC 3 01 0
--补充增加1些数据:
insert into t values(4,'bbbb',1);
insert into t values(5,'cccc',null);
insert into t values(6,null,null);
insert into t(id) values(7);
insert into t(id,name) values(8,'dddd');
insert into t(id,status) values(9,2);
commit ;
SCOTT@test01p> set null NULL
SCOTT@test01p> select rowid,id,SYS_NC00002$,name,status from t;
ROWID ID SYS_NC00002$ NAME STATUS
------------------ ---------- ------------------------------ -------------------- ----------
AAAXdLAAJAAAAmOAAA 1 NULL test 0
AAAXdLAAJAAAAmOAAB 2 01 aaaa 0
AAAXdLAAJAAAAmOAAC 3 01 NULL 0
AAAXdLAAJAAAAmOAAD 4 03 bbbb 1
AAAXdLAAJAAAAmOAAE 5 03 cccc NULL
AAAXdLAAJAAAAmOAAF 6 03 NULL NULL
AAAXdLAAJAAAAmOAAG 7 03 test 0
AAAXdLAAJAAAAmOAAH 8 03 dddd 0
AAAXdLAAJAAAAmOAAI 9 03 test 2
9 rows selected.
--从这个里面我们可以推出内部的运算:
当SYS_NC00002$='01'时name从数据块里面取值.status从select * from sys.ecol$ where tabobj# = 96075 and colnum = 3;
当SYS_NC00002$='03'时name,status从数据块里面取值.
当SYS_NC00002$=NULL时从select * from sys.ecol$ where tabobj#= 96075 ;
我们执行insert into t(id) values(7);实际上缺省值是插入到数据块里面去的.因为SYS_NC00002$='03'.
5.为什么SYS_NC00002$仅仅出现三种情况NULL,'01','03'.在增加1个字段看看.
alter table t add (flag varchar2(1) default 'Y');
SCOTT@test01p> select * from sys.ecol$ where tabobj#= 96075 ;
TABOBJ# COLNUM BINARYDEFVAL GUARD_ID
---------- ---------- -------------------- ----------
96075 3 74657374 0
96075 4 80 1
96075 5 59 2
insert into t(id) values(10);
commit ;
select obj#,col#,segcol#,name,default$,type# from sys.col$ where
obj# in ( select object_id from dba_objects where owner=user and object_name='T')
order by col#;
OBJ# COL# SEGCOL# NAME DEFAULT$ TYPE#
---------- ---------- ---------- -------------------- ------------------------------ ----------
96075 0 2 SYS_NC00002$ NULL 23
96075 1 1 ID NULL 2
96075 2 3 NAME 'test' 1
96075 3 4 STATUS 0 2
96075 4 5 FLAG 'Y' 1
SCOTT@test01p> select rowid,id,SYS_NC00002$,name,status,flag from t;
ROWID ID SYS_NC00002$ NAME STATUS F
------------------ ---------- ------------------------------ -------------------- ---------- -
AAAXdLAAJAAAAmOAAA 1 NULL test 0 Y
AAAXdLAAJAAAAmOAAB 2 01 aaaa 0 Y
AAAXdLAAJAAAAmOAAC 3 01 NULL 0 Y
AAAXdLAAJAAAAmOAAD 4 03 bbbb 1 Y
AAAXdLAAJAAAAmOAAE 5 03 cccc NULL Y
AAAXdLAAJAAAAmOAAF 6 03 NULL NULL Y
AAAXdLAAJAAAAmOAAG 7 03 test 0 Y
AAAXdLAAJAAAAmOAAH 8 03 dddd 0 Y
AAAXdLAAJAAAAmOAAI 9 03 test 2 Y
AAAXdLAAJAAAAmOAAJ 10 07 test 0 Y
10 rows selected.
--SYS_NC00002$使用bit来表示.哪一位为1,就表示那个字段在块中.
alter system checkpoint ;
alter system dump datafile 9 block 2446;
Block header dump: 0x0240098e
Object id on Block? Y
seg/obj: 0x1774b csc: 0x00.72ba3a itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2400988 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0008.000.0000146a 0x01403477.0270.19 --U- 1 fsc 0x0000.0072ba3b
0x02 0x0009.016.00001438 0x01402795.026d.15 C--- 0 scn 0x0000.0072b7fa
bdba: 0x0240098e
data_block_dump,data header at 0xe748064
===============
tsiz: 0x1f98
hsiz: 0x26
pbl: 0x0e748064
76543210
flag=--------
ntab=1
nrow=10
frre=-1
fsbo=0x26
fseo=0x1f19
avsp=0x1eee
tosp=0x1eee
0xe:pti[0] nrow=10 offs=0
0x12:pri[0] offs=0x1f92
0x14:pri[1] offs=0x1f85
0x16:pri[2] offs=0x1f7d
0x18:pri[3] offs=0x1f6d
0x1a:pri[4] offs=0x1f60
0x1c:pri[5] offs=0x1f58
0x1e:pri[6] offs=0x1f49
0x20:pri[7] offs=0x1f3a
0x22:pri[8] offs=0x1f2a
0x24:pri[9] offs=0x1f19
block_row_dump:
tab 0, row 0, @0x1f92
tl: 6 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 2] c1 02
tab 0, row 1, @0x1f85
tl: 13 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 03
col 1: [ 1] 01
col 2: [ 4] 61 61 61 61
tab 0, row 2, @0x1f7d
tl: 8 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 04
col 1: [ 1] 01
tab 0, row 3, @0x1f6d
tl: 16 fb: --H-FL-- lb: 0x0 cc: 4
col 0: [ 2] c1 05
col 1: [ 1] 03
col 2: [ 4] 62 62 62 62
col 3: [ 2] c1 02
tab 0, row 4, @0x1f60
tl: 13 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 06
col 1: [ 1] 03
col 2: [ 4] 63 63 63 63
tab 0, row 5, @0x1f58
tl: 8 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 07
col 1: [ 1] 03
tab 0, row 6, @0x1f49
tl: 15 fb: --H-FL-- lb: 0x0 cc: 4
col 0: [ 2] c1 08
col 1: [ 1] 03
col 2: [ 4] 74 65 73 74
col 3: [ 1] 80
tab 0, row 7, @0x1f3a
tl: 15 fb: --H-FL-- lb: 0x0 cc: 4
col 0: [ 2] c1 09
col 1: [ 1] 03
col 2: [ 4] 64 64 64 64
col 3: [ 1] 80
tab 0, row 8, @0x1f2a
tl: 16 fb: --H-FL-- lb: 0x0 cc: 4
col 0: [ 2] c1 0a
col 1: [ 1] 03
col 2: [ 4] 74 65 73 74
col 3: [ 2] c1 03
tab 0, row 9, @0x1f19
tl: 17 fb: --H-FL-- lb: 0x1 cc: 5
col 0: [ 2] c1 0b
col 1: [ 1] 07
col 2: [ 4] 74 65 73 74
col 3: [ 1] 80
col 4: [ 1] 59
end_of_block_dump
End dump data blocks tsn: 3 file#: 9 minblk 2446 maxblk 2446
6.最后再增加一个字段,没有缺省值的情况看看.
alter table t add (pad varchar2(1) );
SCOTT@test01p> select * from sys.ecol$ where tabobj#= 96075 ;
TABOBJ# COLNUM BINARYDEFVAL GUARD_ID
---------- ---------- -------------------- ----------
96075 3 74657374 0
96075 4 80 1
96075 5 59 2
select obj#,col#,segcol#,name,default$,type# from sys.col$ where
obj# in ( select object_id from dba_objects where owner=user and object_name='T')
order by col#;
OBJ# COL# SEGCOL# NAME DEFAULT$ TYPE#
---------- ---------- ---------- -------------------- ------------------------------ ----------
96075 0 2 SYS_NC00002$ NULL 23
96075 1 1 ID NULL 2
96075 2 3 NAME 'test' 1
96075 3 4 STATUS 0 2
96075 4 5 FLAG 'Y' 1
96075 5 6 PAD NULL 1
6 rows selected.
insert into t(id) values(11);
commit;
SCOTT@test01p> select rowid,id,SYS_NC00002$,name,status,flag,pad from t;
ROWID ID SYS_NC00002$ NAME STATUS F PAD
------------------ ---------- ------------------------------ -------------------- ---------- - ----------
AAAXdLAAJAAAAmOAAA 1 NULL test 0 Y NULL
AAAXdLAAJAAAAmOAAB 2 01 aaaa 0 Y NULL
AAAXdLAAJAAAAmOAAC 3 01 NULL 0 Y NULL
AAAXdLAAJAAAAmOAAD 4 03 bbbb 1 Y NULL
AAAXdLAAJAAAAmOAAE 5 03 cccc NULL Y NULL
AAAXdLAAJAAAAmOAAF 6 03 NULL NULL Y NULL
AAAXdLAAJAAAAmOAAG 7 03 test 0 Y NULL
AAAXdLAAJAAAAmOAAH 8 03 dddd 0 Y NULL
AAAXdLAAJAAAAmOAAI 9 03 test 2 Y NULL
AAAXdLAAJAAAAmOAAJ 10 07 test 0 Y NULL
AAAXdLAAJAAAAmOAAK 11 07 test 0 Y NULL
11 rows selected.
7.总结:
--增加字段带缺省值,在11G下很快完成,不需修改表,但是以后的插入要多付出2个字节的代价(不会增加9个字段带缺省值的吧^_^)
--它通过增加1个隐含字段里面的bit来表示这些字段从那里来读取.
--后续的插入即使insert不带这些字段,其缺省值也插入数据块中.
8.补充看执行计划的内容:
SCOTT@test01p> host cat d:\tools\sqllaji\dpc.sql
set verify off
select * from table(dbms_xplan.display_cursor(NVL('&1',NULL),NULL,'ALLSTATS LAST PEEKED_BINDS &2 cost'));
select rowid,id,SYS_NC00002$,name,status,flag,pad from t;
SCOTT@test01p> @dpc '' projection
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID b4dbk324zz1mj, child number 0
-------------------------------------
select rowid,id,SYS_NC00002$,name,status,flag,pad from t
Plan hash value: 1601196873
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)|
| 1 | TABLE ACCESS FULL| T | 1 | 3 (0)|
--------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - ROWID[ROWID,10], "ID"[NUMBER,22],
"SYS_NC00002$"[RAW,126],
DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00002$",0)),NULL,NVL("NAME",'test'),'0',NVL("NAME",'test'),'1',"NAME")[10],
DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00002$",1)),NULL,NVL("STATUS",0),'0',NVL("STATUS",0),'1',"STATUS")[22],
DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00002$",2)),NULL,NVL("FLAG",'Y'),'0' ,NVL("FLAG",'Y'),'1',"FLAG")[1],
"PAD"[VARCHAR2,1]
-- 我折行为了显示观察的方便,实际上就是使用
-- DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00002$",0)),NULL,NVL("NAME",'test'),'0',NVL("NAME",'test'),'1',"NAME")显示name的值.
-- 许多推论与猜测是一直的.
select rowid,id,SYS_NC00002$,name,DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00002$",0)),NULL,NVL("NAME",'test'),'0',NVL("NAME",'test'),'1',"NAME") from t;
SCOTT@test01p> select rowid,id,SYS_NC00002$,name,DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00002$",0)),NULL,NVL("NAME",'test'),'0',NVL("NAME",'test'),'1',"NAME") from t;
ROWID ID SYS_NC00002$ NAME DECODE(TO)
------------------ ---------- ------------------------------ -------------------- ----------
AAAXdLAAJAAAAmOAAA 1 NULL test test
AAAXdLAAJAAAAmOAAB 2 01 aaaa aaaa
AAAXdLAAJAAAAmOAAC 3 01 NULL NULL
AAAXdLAAJAAAAmOAAD 4 03 bbbb bbbb
AAAXdLAAJAAAAmOAAE 5 03 cccc cccc
AAAXdLAAJAAAAmOAAF 6 03 NULL NULL
AAAXdLAAJAAAAmOAAG 7 03 test test
AAAXdLAAJAAAAmOAAH 8 03 dddd dddd
AAAXdLAAJAAAAmOAAI 9 03 test test
AAAXdLAAJAAAAmOAAJ 10 07 test test
AAAXdLAAJAAAAmOAAK 11 07 test test
11 rows selected.
