Oracle 11g新增not null的欄位比10g快--新特性，oracle11g

Oracle 11g新增not null的欄位比10g快--新特性，oracle11g

   在11g之前增加一個not null的欄位非常慢，在11g之後就非常快了，我們先做一個測試，然後探究下原理。

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 64-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> drop table test purge;
SQL> create table test as select * from dba_objects;
SQL> select count(*) from test;
  COUNT(*)
----------
    151203
SQL> set timing on
SQL> alter table test add col1 char(1000) DEFAULT 'LARGE COLUMN' not null;
經過時間:  00: 00: 09.48

SQL> SELECT SUM(BYTES)/1024/1024 ||'M' FROM user_segments WHERE segment_name = 'TEST';
SUM(BYTES)/1024/1024||'M'
-----------------------------------------
200M

在11g下：
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> drop table test purge;
SQL> create table test as select * from dba_objects;
SQL> insert into test select * from dba_objects;
SQL> commit;
SQL> select count(*) from test;
  COUNT(*)
----------
    149012
SQL> set timing on
SQL> alter table test add col1 char(1000) DEFAULT 'LARGE COLUMN' not null;

經過時間:  00: 00: 00.07

SQL> SELECT SUM(BYTES)/1024/1024 ||'M' FROM user_segments WHERE segment_name = 'TEST';
SUM(BYTES)/1024/1024||'M'
-----------------------------------------

18M

探究原理：dump block可以發現，在10g下產生了很多行遷移和行連結。而在11g中就是一個值，在官方文檔中說是Oracle通過在資料字典中記錄DEFAULT值，避免了繁重的更新操作。

在dump block看看：
alter session set tracefile_identifier = 'gg_test';
select rowid,
       dbms_rowid.rowid_object(rowid) object_id,
       dbms_rowid.rowid_relative_fno(rowid) file_id,
       dbms_rowid.rowid_block_number(rowid) block_id,
       dbms_rowid.rowid_row_number(rowid) num
  from test where rownum <5;
alter system dump datafile 5 block 1465683;

10g下：
tl: 9 fb: --H----- lb: 0x2  cc: 0
nrid:  0x0181e36b.1
tab 0, row 2, @0x1b3a
tl: 1076 fb: --H-FL-- lb: 0x2  cc: 14
col  0: [ 3]  53 59 53
col  1: [ 4]  43 4f 4e 24
col  2: *NULL*
col  3: [ 2]  c1 1d
col  4: [ 2]  c1 1d
col  5: [ 5]  54 41 42 4c 45
col  6: [ 7]  78 69 08 1e 0e 33 19
col  7: [ 7]  78 69 08 1e 0f 31 37
col  8: [19]  32 30 30 35 2d 30 38 2d 33 30 3a 31 33 3a 35 30 3a 32 34
col  9: [ 5]  56 41 4c 49 44
col 10: [ 1]  4e
col 11: [ 1]  4e
col 12: [ 1]  4e
col 13: [1000]
 4c 41 52 47 45 20 43 4f 4c 55 4d 4e 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
tab 0, row 3, @0x1b31
tl: 9 fb: --H----- lb: 0x2  cc: 0
nrid:  0x0181e36b.2  --nrid行遷移
tab 0, row 4, @0x1b28
tl: 9 fb: --H----- lb: 0x2  cc: 0
nrid:  0x0181e36b.3

11g下：
tab 0, row 0, @0x3f33
tl: 77 fb: --H-FL-- lb: 0x0  cc: 14
col  0: [ 3]  53 59 53
col  1: [ 5]  49 43 4f 4c 24
col  2: *NULL*
col  3: [ 2]  c1 15
col  4: [ 2]  c1 03
col  5: [ 5]  54 41 42 4c 45
col  6: [ 7]  78 72 06 09 0c 1a 2f
col  7: [ 7]  78 72 06 09 0c 26 33
col  8: [19]  32 30 31 34 2d 30 36 2d 30 39 3a 31 31 3a 32 35 3a 34 36
col  9: [ 5]  56 41 4c 49 44
col 10: [ 1]  4e
col 11: [ 1]  4e
col 12: [ 1]  4e
col 13: [ 2]  c1 02
tab 0, row 1, @0x3ee4
tl: 79 fb: --H-FL-- lb: 0x0  cc: 14
col  0: [ 3]  53 59 53
col  1: [ 7]  49 5f 55 53 45 52 31
col  2: *NULL*
col  3: [ 2]  c1 2f
col  4: [ 2]  c1 2f
col  5: [ 5]  49 4e 44 45 58
col  6: [ 7]  78 72 06 09 0c 1a 2f
col  7: [ 7]  78 72 06 09 0c 1a 2f
col  8: [19]  32 30 31 34 2d 30 36 2d 30 39 3a 31 31 3a 32 35 3a 34 36
col  9: [ 5]  56 41 4c 49 44
col 10: [ 1]  4e
col 11: [ 1]  4e
col 12: [ 1]  4e
col 13: [ 2]  c1 05

給oracle的date欄位加not null

alter table modify a not null;
要這樣才行。
不過前提如果這列中有空值了 你要先把空值刪除 才能修改

補充，順便記下oracle中對列的各種操作吧：
1.增加列

ALTER TABLE table_name ADD( column datatype [DEFAULT EXPR][,column datatype...]);

例如:

SQL>ALTER TABLE emp01 ADD eno NUMBER(4);

2.修改列定義

例如:

SQL>ALTER TABLE emp01 MODIFY job VARCHAR2(15)

2 DEFAULT 'CLERK'

3.刪除列

例如:

SQL> ALTER TABLE emp01 DROP COLUMN dno;

4.修改列名

例如:

SQL>ALTER TABLE emp01 RENAME COLUMN eno TO empno;

5.修改表名

例如:

SQL>RENAME emp01 TO employee;

6.增加註釋

例如:

SQL>COMMENT ON TABLE employee IS '存放僱員資訊';

SQL>COMMENT ON TABLE employee.name IS '描述僱員姓名';
 
oracle更改列屬性:把某列之前設定的null改為not null；如下是我的操作：報錯

alter table tablename modify (column datatype [default value][null/not null],….);
所以應該加上現在的欄位類型