11g_NotNull_Columns_with_Default_Values

zhyuh發表於2009-09-27
Null
先看以前版本中，增加有預設值的非空列有哪些影響。

準備環境。建表trans，插入10萬條記錄
SQL> create table trans (
cust_name varchar2(20),
trans_dt date,
trans_amt number(12,2),
store_id number(2)
)
/
Table created
.
SQL> declare
2 l_stmt varchar2(2000);
3 begin
4 for ctr in 1..100000 loop
5 l_stmt := 'insert into trans values ('||
6 ''''||dbms_random.string('U',20)||''','||
7 'sysdate - '||
8 round(dbms_random.value(1,365))||','||
9 round(dbms_random.value(1,99999999),2)||','||
10 round(dbms_random.value(1,99))||')';
11 dbms_output.put_line(l_stmt);
12 execute immediate l_stmt;
13 commit;
14 end loop;
15 end;
16 /
PL/SQL procedure successfully completed [@more@]**********
* 10g
**********
SQL> select count(*) from dba_extents where segment_name='TRANS';
COUNT(*)
----------
39

SQL> select sid from v$mystat where rownum<2;
SID
----------
1107

SQL> alter table trans add col5 varchar2(20) default 'abcdefghijklmnopqrst' not null;
Table altered
Executed in 23.955 seconds

SQL> select count(*) from dba_extents where segment_name='TRANS';
COUNT(*)
----------
65

--another session
SQL> select * from v$transaction where addr=(select taddr from v$session where sid=1107);
USED_UBLK
----------
2134

可以看出，在10g中，
1. 表trans原先佔用39個extents，增加col5後佔用65個extents，多了26個(local management表空間，在extent數量達到一定級別以前每個extent大小都是一樣的，被實驗中每個extent大小我也確認過，確實都是一樣的，128k)。
2. 增加col5這個操作用了近24秒鐘。
3. 增加col5過程中，從另外一個session中觀察到最多使用了2134個undo block。


**********
* 11g
**********
SQL> select count(*) from dba_extents where segment_name='TRANS';

COUNT(*)
----------
21

SQL> alter table trans add col5 varchar2(20) default 'abcdefghijklmnopqrst' not
null;
Table altered.
Elapsed: 00:00:00.63

SQL> select count(*) from dba_extents where segment_name='TRANS';
COUNT(*)
----------
21

在11g中，
1. 增加col5前後佔用extent的數量沒有變化。
2. 增加col5這個動作不到1秒鐘就完成了。
3. 沒有觀察到undo的使用情況（時間太短來不及觀察？其實是根本就沒有使用undo tablespace）。

在11g中，對於原先的10萬條記錄，"add col5 varchar2(20) default 'abcdefghijklmnopqrst' not null"這個操作並沒有把值'abcdefghijklmnopqrst'真正加到每條記錄中，所以執行需要的時間很短，空間也沒有什麼變化。但是使用者選取原先的紀錄時，能不能看到新增加的列col5和值呢？能。Oracle會根據資料字典的定義給出col5的預設值。

這個新特性用到的時候還是很好的。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/207/viewspace-1027390/，如需轉載，請註明出處，否則將追究法律責任。