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

小人物 · 发表于 2007-8-20 18:28

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$ 中查找默认值来返回.

以上是今天看到这个特征测试的结果,有什么疏漏欢迎大家指证.