往表中插入大量的資料的方法（二）

pingley發表於2012-05-16
往表中插入大量的資料的方法（二）
雖然可以使用insert append 的方式來往一張表中插入大量的資料，但是
畢竟存在諸多的限制和缺陷，下面討論另外一種方式。先建立一張測試表。
並新增主鍵約束。
SQL> ed
Wrote file afiedt.buf
  1  create table t_insert as select object_id,object_name
  2  from dba_objects
  3* where 1 = 2
SQL> /
Table created.
SQL> ed
Wrote file afiedt.buf
  1* alter table t_insert add constraint t_insert_pk primary key(object_id)
SQL> /
Table altered.
嘗試往測試表中插入較大量的資料，為了節省時間我們把插入的資料量設定為10W。加大資料量
對比的結果會更加的明顯。
SQL> set timing on
SQL> ed
Wrote file afiedt.buf
  1   begin
  2     for i in 1..100000
  3     loop
  4       insert into t_insert values(i,rpad('A',100,'A'));
  5     end loop;
  6*  end;
  7  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:14.40
SQL> rollback;
Rollback complete.
Elapsed: 00:00:06.41
下面把主鍵約束disable，在並查詢user_indexes 表。也許你還沒有反應過來
為什麼要查詢user_indexes 表，待會就明白了。
SQL> alter table t_insert disable constraint t_insert_pk;
Table altered.
Elapsed: 00:00:00.32
SQL> select index_name,status
  2  from user_indexes
  3  where table_name = 'T_INSERT';
no rows selected
Elapsed: 00:00:00.21
嘗試往測試表中插入相同的資料庫，並比對兩次插入的用時。可以發現當我們把
主鍵約束給disable 以後，insert語句執行的效率提高了1倍多，這是非常可觀的
特別是當資料量很大的時候。
SQL> ed
Wrote file afiedt.buf
  1  begin
  2    for i in 1..100000
  3    loop
  4      insert into t_insert values(i,rpad('A',100,'A'));
  5    end loop;
  6* end;
SQL> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:06.30
SQL> rollback；
Rollback complete.
或許我們還可以把這種方式和insert append的方式結合起來一起使用。下面
我們來進行這個實驗吧。這次的插入用時更少，但是改善的並不明顯，因為
資料量還不夠大。我們在插入前，disable 了表中的pk 約束，現在插入完成了
我們應該enable。
SQL> ed
Wrote file afiedt.buf
  1  begin
  2    for i in 1..100000
  3    loop
  4      insert /*+append*/into t_insert values(i,rpad('A',100,'A'));
  5    end loop;
  6* end;
SQL> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.52
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
SQL> alter table t_insert enable constraint t_insert_pk;
Table altered.
Elapsed: 00:00:00.46
做下面這個查詢，我想你已經明白為什麼前面要做相同的查詢了。
因為oracle 會在主鍵上面自動的建立唯一索引，可見當我們disable
主鍵約束的時候，相應的把索引給刪除了。本質上是因為沒有所以
所以上面的insert 語句執行起來才會那麼飄逸。
SQL> select index_name,status
  2  from user_indexes
  3  where table_name = 'T_INSERT';
INDEX_NAME                                                   STATUS
------------------------------------------------------------ ----------------
T_INSERT_PK                                                  VALID
Elapsed: 00:00:00.00
你可以使用下面的小指令碼來獲得表中的索引，並unusable 以免影響insert插入的效能。
下面是一個在HR 模式下的一個示例，當插入完成以後，執行相應的rebulid 語句來使
索引生效。另外一種可選的方式是先將待插入資料的表中的index drop 掉，以後使用
相應的sql 語句重建，不過在你這樣做之前，確保你得到了重建索引需要的指令碼。
select 'alter index ' || index_name || ' unusable;'
from user_indexes where table_name=upper('&table_name');
SQL> ed
Wrote file afiedt.buf
  1  select 'alter index ' || index_name || 'unusable;'
  2* from user_indexes where table_name=upper('&table_name')
SQL> /
Enter value for table_name: employees
old   2: from user_indexes where table_name=upper('&table_name')
new   2: from user_indexes where table_name=upper('employees')
'ALTERINDEX'||INDEX_NAME||'UNUSABLE;'
-----------------------------------------------------------------
alter index EMP_EMAIL_UK unusable;
alter index EMP_EMP_ID_PK unusable;
select 'alter index ' || index_name || ' rebuild;'
from user_indexes where table_name=upper('&table_name');
由剛才的primary key ，index 腦中飄過一個詞約束。。。約束會對insert 語句有影響嗎？
我們來嘗試下，眼見為實。除了primary key 和 unique  約束oracle 會自動的為這些列建立
index（並不是總是會建立，也存在例外的），其他約束的列不會隱式的為其建立索引。
SQL> drop table t_insert;
Table dropped.
SQL> create table t_insert (x int check (x >= 100));
Table created.
SQL> create table t_insert1 (x int);
Table created.
SQL>  select index_name from user_indexes
  2   where table_name in ('T_INSERT','T_INSERT1');
no rows selected
SQL> ed
Wrote file afiedt.buf
  1  begin
  2    for i in 110..300000
  3    loop
  4      insert into t_insert values(i);
  5    end loop;
  6* end;
SQL> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:40.18
SQL> ed
Wrote file afiedt.buf
  1  begin
  2    for i in 110..300000
  3    loop
  4      insert into t_insert1 values(i);
  5    end loop;
  6* end;
SQL> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:36.17
透過上面的對比我們可以知道約束會影響insert 語句，因為資料庫要花費資源來
檢查插入的資料是否符合表中的約束的定義。
小結：在這裡我們已經討論了索引對insert語句的影響，對此如果需要插入大量的資料
可以把索引unusable,插入完以後rebuild。並知道表中的約束也會影響insert語句的
效能，但是確定要使用上述的方式insert大量資料的時候，你必須清楚你要插入的資料
是怎樣的，有沒有違反表中的約束的定義。不應該只追求效能，而不顧資料的正確性。

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

相關文章