首先我们看一组测试 数据库版本oracle 12c beta1
SQL> select count(*) from test;
COUNT(*)
----------
725888
SQL> set timing on;
SQL> alter table test add name varchar(20) default 'liu';
Table altered.
Elapsed: 00:00:00.02
Elapsed: 00:00:00.01
SQL> alter table test add name1 varchar(20) default 'liuyang' not null;
Table altered.
Elapsed: 00:00:00.02
在12c oracle再次加强add column操作,在11g中oracle add column with default value 必须加上not null约束,而使得oracle会直接去col$访问default$来得到default value,避免了全表update。而在12c中,oracle再次加强了这一特性。看下面的测试
session 1 run on 12.1.0.1.0
[oracle@db-42 trace]$ ora si SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 27 16:43:16 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> create table t3 (id int) tablespace utest; Table created. SQL> alter table t3 add name varchar(20) default 'liu'; Table altered. SQL> insert into t3 (id) values (1); 1 row created. SQL> insert into t3 values (1,null); 1 row created. SQL> commit; Commit complete. SQL> select rowid, dbms_rowid.rowid_relative_fno(rowid)rel_fno, dbms_rowid.rowid_block_number(rowid)blockno, dbms_rowid.rowid_row_number(rowid) rowno from t3 ; 2 3 4 5 ROWID REL_FNO BLOCKNO ROWNO ------------------ ---------- ---------- ---------- AAAWeGAAOAAAACDAAA 14 131 0 AAAWeGAAOAAAACDAAB 14 131 1 SQL> alter system flush buffer_cache; System altered. SQL> alter system dump datafile 14 block 131; System altered.
session 2 run on 11.2.0.3
[oracle@db-42 trace]$ ora si SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 27 16:40:09 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create table t3 (id int) tablespace utest; Table created. SQL> alter table t3 add name varchar(20) default 'liu'; Table altered. SQL> insert into t3 (id) values (1); 1 row created. SQL> insert into t3 values (1,null); 1 row created. SQL> commit; Commit complete. SQL> select rowid, dbms_rowid.rowid_relative_fno(rowid)rel_fno, dbms_rowid.rowid_block_number(rowid)blockno, dbms_rowid.rowid_row_number(rowid) rowno from T3 ; 2 3 4 5 ROWID REL_FNO BLOCKNO ROWNO ------------------ ---------- ---------- ---------- AAAToBAAHAAAACDAAA 7 131 0 AAAToBAAHAAAACDAAB 7 131 1 SQL> alter system flush buffer_cache; System altered. SQL> alter system dump datafile 7 block 131; System altered.
oracle在block上增加了一个标识位,从而避免了没有not null时的访问矛盾问题(with null value) (oracle will know when to visit dictionary)
well done