Oracle 11g 快速加带默认值的列

作者：  小人物  出处：www.itpub.net

 

oracle 11g 对加带默认值速度上有很大提升,来测试一下这个特征和原理:

--11g
SQL> alter session set sql_trace=true;

Session altered.

SQL> alter table gark add name varchar2(10) default 'hello' not null;

Table altered.

--tracefile
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       33      0.03       0.21          0          0          0           0
Execute     45      0.05       0.14          3         33         46          10
Fetch       39      0.00       0.01          0         89          0         266
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      117      0.09       0.36          3        122         46         276

--可以看到current跟query都很小,也只有47个内部调用.耗时不到1s
--在tracefile里面没有找到update gark set name='hello'语句.

--10g
SQL> alter session set sql_trace=true;                                                                                             

Session altered.

SQL> alter table gark add name varchar2(10) default 'hello' not null;                                                               

Table altered.

--tracefile
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       49      0.03       0.02          0          0          0           0
Execute     69      3.71       3.62          0      59546     407425      100009
Fetch       93      0.00       0.00          0        181          0          68
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      211      3.75       3.66          0      59727     407425      100077

--主要是里面的update gark set name='hello'消耗了大部分的时间.

--可以看到11g并没有更新实际列的内容,那么查询的时候是否有别的操作

--sql_trace没能跟踪出来,改成10046看看:
alter session set events '10046 trace name context forever,level 8';
select * from gark where rownum<100;
alter session set events '10046 trace name context off';

--发现11g中除了:
SQL ID : 7u4jgnqn97ax2
select *
from
gark where rownum<100


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        8      0.00       0.00          0         15          0          99
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       10      0.00       0.00          0         17          0          99
--前面多了这条语句:
SQL ID : aa35g82k7dkd9
select binaryDefVal, length(binaryDefVal)
from
ecol$           where tabobj# = :1 and colnum = :2


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          2          0           1

--在查询前会先去ecol$ 中找到该列的默认值.

--在11g中dump一个数据库,然后更新name字段,在dump该块,结果如下.

--11g 更新前数据块内容
block_row_dump:
tab 0, row 0, @0x1f89
tl: 12 fb: --H-FL-- lb: 0x2  cc: 3
col  0: [ 2]  c1 02
col  1: [ 2]  c1 02
col  2: [ 2]  c1 02
tab 0, row 1, @0x1f7a
tl: 12 fb: --H-FL-- lb: 0x2  cc: 3
col  0: [ 2]  c1 03
col  1: [ 2]  c1 03
col  2: [ 2]  c1 03

--11g 更新后数据块内容
block_row_dump:
tab 0, row 0, @0x1f8c
tl: 12 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 02
col  1: [ 2]  c1 02
col  2: [ 2]  c1 02
tab 0, row 1, @0x8bb
tl: 18 fb: --H-FL-- lb: 0x1  cc: 4
col  0: [ 2]  c1 03
col  1: [ 2]  c1 03
col  2: [ 2]  c1 03
col  3: [ 5]  77 6f 72 6c 64

--10g 更新前数据块里就已经有该列信息.
block_row_dump:
tab 0, row 0, @0x1f86
tl: 18 fb: --H-FL-- lb: 0x2  cc: 4
col  0: [ 2]  c1 02
col  1: [ 2]  c1 02
col  2: [ 2]  c1 02
col  3: [ 5]  68 65 6c 6c 6f
tab 0, row 1, @0x1f74
tl: 18 fb: --H-FL-- lb: 0x2  cc: 4
col  0: [ 2]  c1 03
col  1: [ 2]  c1 03
col  2: [ 2]  c1 03
col  3: [ 5]  68 65 6c 6c 6f

--总结:
--oracle 11g在加上默认值后数据块中并没有存储这些数据,oracle在查询的时候如果数据块中
--没有对应的列就会先去ecol$ 中查找默认值来返回.