表列新增預設值的方法
在修改表結構時，有時候會涉及到新增預設值。
下面是在11.2.0.3版本資料庫中進行測試的結果：
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as borpt
SQL> set timing on
SQL> select count(1) from user_mark_resultt;
COUNT(1)
----------
1451535
Executed in 0.219 seconds
當前資料量為145萬
操作方法1：先增加表列，再修改預設值
SQL> alter table user_mark_resultadd name2 varchar2(10) ;
Table altered
Executed in 0.047 seconds
SQL> alter table user_mark_result modify name2 default 'a';
Table altered
Executed in 0.063 seconds
操作方法2：直接增加表列並賦予預設值
SQL> alter table user_mark_result add name3 varchar2(10) default 'a' ;
Table altered
Executed in 45.86 seconds
結果如下：
SQL> select t.dealerid,t.name2,t.name3 from user_mark_result t where rownum<5;
DEALERID NAME2 NAME3
------------------------------ ---------- ----------
DL0120100619 a
DL0120100621 a
DL0120100628 a
DL0120100904 a
Executed in 0.062 seconds
通過以上比較可知：
1.為表增加欄位後，再修改預設值，已存在的資料不受影響
2.增加欄位語句中同時為欄位新增預設值，需要較長時間的鎖表，這在生產系統上通常是不可接受的
因此，在oracle中進行表的新增列預設值操作時，為減少鎖表時間，需要分三步操作：
1. 向表中新增欄位帶有default值時，做如下修改
alter table user_mark_result add name2 varchar2(10);
2.在欄位新增完成後，分多個小事務更新新加的欄位值，避免鎖表。可以建立錯誤日誌記錄表，記錄可能發生的錯誤：
create table err_log(status varchar2(200));
declare
n1 number :=0;
v_str varchar2(200);
begin
for i in (select dealerid from user_mark_result)
loop
n1 := n1+1;
update user_mark_result set name2 = 'a' where dealerid = i.dealerid;
if mod(n1,5000)=0 then
commit;
end if;
end loop;
commit;
exception
when others then
rollback;
v_str := SQLCODE || '_' || SQLERRM;
insert into err_log (status) values(v_str);
commit;
end;
/
3.如果上述匿名塊正常執行，則刪除err_log表，完成操作；否則根據err_log中的錯誤記錄進行相應修改。
4.增加表列的預設值
alter table user_mark_result modify name2 default 'a';
另外，上面的更新方法步驟2需要在每天更新語句後進行判斷，如果使用遊標處理，每次更新5000條資料，則效率更高。
以下是用order_table測試的結果：
SQL> select count(1),to_char(e.order_time,'yyyy-mm-dd')
2 from order_table e
3 where e.order_time>=to_date('20150401','yyyymmdd')
4 and e.order_time<to_date('20150501','yyyymmdd')
5 group by to_char(e.order_time,'yyyy-mm-dd')
6 order by to_char(e.order_time,'yyyy-mm-dd');
COUNT(1) TO_CHAR(E.ORDER_TIME,'YYYY-MM-
---------- ------------------------------
1438047 2015-04-01
1312835 2015-04-02
1167460 2015-04-03
921161 2015-04-04
802476 2015-04-05
750283 2015-04-06
682537 2015-04-07
651092 2015-04-08
629104 2015-04-09
667710 2015-04-10
648531 2015-04-11
658504 2015-04-12
593864 2015-04-13
576714 2015-04-14
589528 2015-04-15
612004 2015-04-16
636615 2015-04-17
631522 2015-04-18
673595 2015-04-19
649317 2015-04-20
645253 2015-04-21
635374 2015-04-22
668124 2015-04-23
658031 2015-04-24
666737 2015-04-25
729222 2015-04-26
736643 2015-04-27
740636 2015-04-28
739578 2015-04-29
1013999 2015-04-30
30 rows selected
通過以上查詢，4月5日~12日的資料量為4831733條，
4月22日~28日的資料量為4834767條，基本相同，作為測試資料。
1）建立錯誤記錄表：
SQL> drop table err_log;
Table dropped
Executed in 0.063 seconds
SQL> create table err_log(status varchar2(200));
Table created
Executed in 0.032 seconds
SQL> select * from err_log;
STATUS
--------------------------------------------------------------------------------
Executed in 0.016 seconds
2）使用遊標的方法進行資料更新：
SQL> DECLARE
2 TYPE typ_order_id IS TABLE OF order_table.order_id%type;
3 order_id typ_order_id;
4 v_str varchar2(200);
5 cursor cur_order is --宣告遊標
6 SELECT order_id
7 FROM order_table e
8 where e.order_time > to_date('20150405', 'yyyymmdd')
9 and e.order_time < to_date('20150412', 'yyyymmdd');
10 BEGIN
11 open cur_order; --開啟遊標
12 loop
13 fetch cur_order BULK COLLECT --將資料插入陣列，每5000條批量插入一次
14 INTO order_id limit 5000;
15 forall i in order_id.first .. order_id.last --對這5000條資料進行批量更新
16 update order_table e
17 set e.new_price = 0
18 where e.order_id = order_id(i);
19 commit;
20 exit when cur_order%notfound;
21 end loop;
22 close cur_order; --迴圈結束後，關閉遊標
23 exception
24 when others then
25 rollback;
26 v_str := SQLCODE || '_' || SQLERRM;
27 insert into err_log (status) values(v_str);
28 commit;
29 END;
30 /
PL/SQL procedure successfully completed
Executed in 738.281 seconds
使用逐條更新，每5000條提交一次的方法：
SQL> declare
2 n1 number :=0;
3 v_str varchar2(200);
4 begin
5 for i in (SELECT order_id
6 FROM order_table e
7 where e.order_time > to_date('20150422', 'yyyymmdd')
8 and e.order_time < to_date('20150429', 'yyyymmdd') )
9 loop
10 n1 := n1+1;
11 update order_table set new_price = 0 where order_id = i.order_id;
12 if mod(n1,5000)=0 then
13 commit;
14 end if;
15 end loop;
16 commit;
17 exception
18 when others then
19 rollback;
20 v_str := SQLCODE || '_' || SQLERRM;
21 insert into err_log (status) values(v_str);
22 commit;
23 end;
24 /
PL/SQL procedure successfully completed
Executed in 901.36 seconds
SQL> select * from err_log;
STATUS
--------------------------------------------------------------------------------
Executed in 0.016 seconds
SQL> drop table err_log;
Table dropped
Executed in 0.094 seconds
使用遊標的方法比逐條插入的方法快了約160s，效能差距還是比較明顯的。
下面是在11.2.0.3版本資料庫中進行測試的結果：
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as borpt
SQL> set timing on
SQL> select count(1) from user_mark_resultt;
COUNT(1)
----------
1451535
Executed in 0.219 seconds
當前資料量為145萬
操作方法1：先增加表列，再修改預設值
SQL> alter table user_mark_resultadd name2 varchar2(10) ;
Table altered
Executed in 0.047 seconds
SQL> alter table user_mark_result modify name2 default 'a';
Table altered
Executed in 0.063 seconds
操作方法2：直接增加表列並賦予預設值
SQL> alter table user_mark_result add name3 varchar2(10) default 'a' ;
Table altered
Executed in 45.86 seconds
結果如下：
SQL> select t.dealerid,t.name2,t.name3 from user_mark_result t where rownum<5;
DEALERID NAME2 NAME3
------------------------------ ---------- ----------
DL0120100619 a
DL0120100621 a
DL0120100628 a
DL0120100904 a
Executed in 0.062 seconds
通過以上比較可知：
1.為表增加欄位後，再修改預設值，已存在的資料不受影響
2.增加欄位語句中同時為欄位新增預設值，需要較長時間的鎖表，這在生產系統上通常是不可接受的
因此，在oracle中進行表的新增列預設值操作時，為減少鎖表時間，需要分三步操作：
1. 向表中新增欄位帶有default值時，做如下修改
alter table user_mark_result add name2 varchar2(10);
2.在欄位新增完成後，分多個小事務更新新加的欄位值，避免鎖表。可以建立錯誤日誌記錄表，記錄可能發生的錯誤：
create table err_log(status varchar2(200));
declare
n1 number :=0;
v_str varchar2(200);
begin
for i in (select dealerid from user_mark_result)
loop
n1 := n1+1;
update user_mark_result set name2 = 'a' where dealerid = i.dealerid;
if mod(n1,5000)=0 then
commit;
end if;
end loop;
commit;
exception
when others then
rollback;
v_str := SQLCODE || '_' || SQLERRM;
insert into err_log (status) values(v_str);
commit;
end;
/
3.如果上述匿名塊正常執行，則刪除err_log表，完成操作；否則根據err_log中的錯誤記錄進行相應修改。
4.增加表列的預設值
alter table user_mark_result modify name2 default 'a';
另外，上面的更新方法步驟2需要在每天更新語句後進行判斷，如果使用遊標處理，每次更新5000條資料，則效率更高。
以下是用order_table測試的結果：
SQL> select count(1),to_char(e.order_time,'yyyy-mm-dd')
2 from order_table e
3 where e.order_time>=to_date('20150401','yyyymmdd')
4 and e.order_time<to_date('20150501','yyyymmdd')
5 group by to_char(e.order_time,'yyyy-mm-dd')
6 order by to_char(e.order_time,'yyyy-mm-dd');
COUNT(1) TO_CHAR(E.ORDER_TIME,'YYYY-MM-
---------- ------------------------------
1438047 2015-04-01
1312835 2015-04-02
1167460 2015-04-03
921161 2015-04-04
802476 2015-04-05
750283 2015-04-06
682537 2015-04-07
651092 2015-04-08
629104 2015-04-09
667710 2015-04-10
648531 2015-04-11
658504 2015-04-12
593864 2015-04-13
576714 2015-04-14
589528 2015-04-15
612004 2015-04-16
636615 2015-04-17
631522 2015-04-18
673595 2015-04-19
649317 2015-04-20
645253 2015-04-21
635374 2015-04-22
668124 2015-04-23
658031 2015-04-24
666737 2015-04-25
729222 2015-04-26
736643 2015-04-27
740636 2015-04-28
739578 2015-04-29
1013999 2015-04-30
30 rows selected
通過以上查詢，4月5日~12日的資料量為4831733條，
4月22日~28日的資料量為4834767條，基本相同，作為測試資料。
1）建立錯誤記錄表：
SQL> drop table err_log;
Table dropped
Executed in 0.063 seconds
SQL> create table err_log(status varchar2(200));
Table created
Executed in 0.032 seconds
SQL> select * from err_log;
STATUS
--------------------------------------------------------------------------------
Executed in 0.016 seconds
2）使用遊標的方法進行資料更新：
SQL> DECLARE
2 TYPE typ_order_id IS TABLE OF order_table.order_id%type;
3 order_id typ_order_id;
4 v_str varchar2(200);
5 cursor cur_order is --宣告遊標
6 SELECT order_id
7 FROM order_table e
8 where e.order_time > to_date('20150405', 'yyyymmdd')
9 and e.order_time < to_date('20150412', 'yyyymmdd');
10 BEGIN
11 open cur_order; --開啟遊標
12 loop
13 fetch cur_order BULK COLLECT --將資料插入陣列，每5000條批量插入一次
14 INTO order_id limit 5000;
15 forall i in order_id.first .. order_id.last --對這5000條資料進行批量更新
16 update order_table e
17 set e.new_price = 0
18 where e.order_id = order_id(i);
19 commit;
20 exit when cur_order%notfound;
21 end loop;
22 close cur_order; --迴圈結束後，關閉遊標
23 exception
24 when others then
25 rollback;
26 v_str := SQLCODE || '_' || SQLERRM;
27 insert into err_log (status) values(v_str);
28 commit;
29 END;
30 /
PL/SQL procedure successfully completed
Executed in 738.281 seconds
使用逐條更新，每5000條提交一次的方法：
SQL> declare
2 n1 number :=0;
3 v_str varchar2(200);
4 begin
5 for i in (SELECT order_id
6 FROM order_table e
7 where e.order_time > to_date('20150422', 'yyyymmdd')
8 and e.order_time < to_date('20150429', 'yyyymmdd') )
9 loop
10 n1 := n1+1;
11 update order_table set new_price = 0 where order_id = i.order_id;
12 if mod(n1,5000)=0 then
13 commit;
14 end if;
15 end loop;
16 commit;
17 exception
18 when others then
19 rollback;
20 v_str := SQLCODE || '_' || SQLERRM;
21 insert into err_log (status) values(v_str);
22 commit;
23 end;
24 /
PL/SQL procedure successfully completed
Executed in 901.36 seconds
SQL> select * from err_log;
STATUS
--------------------------------------------------------------------------------
Executed in 0.016 seconds
SQL> drop table err_log;
Table dropped
Executed in 0.094 seconds
使用遊標的方法比逐條插入的方法快了約160s，效能差距還是比較明顯的。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26451536/viewspace-1815860/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 為 protocol 中屬性新增預設值Protocol
- Entity Framework的預設值BUG解決方法Framework
- MySQL設定當前時間為預設值的方法MySql
- ${VAR:=預設值}和${VAR:-預設值} 區別
- Go：變數宣告的多種方法與預設值Go變數
- HTML 元素的預設值HTML
- 【譯】GOPATH 的預設值Go
- MySQL多個timestamp欄位自動新增預設值的問題MySql
- MySQL-修改欄位型別、設定預設值，以及新增註釋MySql型別
- PostgreSQL11preview-新增非空預設值不需要rewritetableSQLView
- HTML常用元素的預設值HTML
- sqlserver 修改列的預設值SQLServer
- phpcheckbox核取方塊值的獲取與checkbox預設值輸出方法PHP
- Oracle資料表預設值列新增與行遷移（Row Migration）Oracle
- 【C++】引數的預設值C++
- css的border屬性預設值CSS
- Laravel 生成 url,預設值Laravel
- HTTP請求預設值HTTP
- sql設定欄位預設值SQL
- django 設定預設值到SQLDjangoSQL
- 預設方法的使用模式模式
- Python中的預設引數值Python
- Oracle中NLS_LANG的預設值Oracle
- SQL SERVER檢視列的預設值SQLServer
- Navicat for MySQL 15使用教程：何時使用預設值以及如何選用恰當的預設值MySql
- C#實現函式預設值和C#4.0實現預設值C#函式
- 由ora-30036引出的問題，給大表新增列的時候，不要設定預設值
- mysql datetime增加預設值MySql
- CSS border屬性預設值CSS
- MySQL修改欄位預設值MySql
- 預測性分析的價值、方法和趨勢
- 對錶中的欄位設定了預設值，新增記錄後卻發現該欄位為nullNull
- 轉貼楊大師yangtingkun＿如何為table快速新增一個預設值的新列
- Golang技巧之預設值設定的高階玩法Golang
- Recoil 中預設值的正確處理
- CSS變數的作用域和預設值CSS變數
- 表列授權
- 決策方法 | 計算預期價值