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

pingley發表於2012-05-10
往表中插入大量資料的方法（一）
下面這個表是根據all_objects 表的結構構造的。用來模擬大量的資料。
SQL> select count(*) from allobjects;
  COUNT(*)
----------
   3375996
Elapsed: 00:00:07.71
建立兩張與allobjcts 結構相同的表。
SQL> edit
Wrote file afiedt.buf
  1  create table alltest01 as select * from allobjects
  2* where 1=2
SQL> /
Table created.
Elapsed: 00:00:00.06
SQL> edit
Wrote file afiedt.buf
  1  create table alltest02 as select * from allobjects
  2* where 1=2
SQL> /
Table created.
Elapsed: 00:00:00.08
使用傳統的insert 語句往alltest01 插入大量的資料。
SQL> insert into alltest01 select * from allobjects;
3375996 rows created.
Elapsed: 00:01:12.60
使用insert append 方式往alltest02 插入大量的資料。
SQL> insert /*+ append */ into alltest02 select * from allobjects;
3375996 rows created.
Elapsed: 00:00:53.78
透過上述的兩條insert 語句我們可以明顯的發現效能上面的差別。傳統的
insert 方式比insert append 方式慢了19秒左右，兩者不在同一個數量級上。
貌似insert append 方式在插入資料方面很有效能優勢。但是除了效能方面
的優勢，存在以下的一些限制：
一、在commit或者rollback前當前不能對錶進行查詢，和執行DML語句。
其他會話中可以進行查詢，但是看到的是insert append 以前的資料，並且
其他會話中執行的DML 語句會被阻塞。另外一個時間點只能有一個會話對
指定的表發起insert append.
SQL> select count(*) from alltest02;
select count(*) from alltest02
                     *
ERROR at line 1:
ORA-12838: 無法在並行模式下修改之後讀/修改物件
Elapsed: 00:00:00.04
SQL> delete from alltest02;
delete from alltest02
            *
ERROR at line 1:
ORA-12838: 無法在並行模式下修改之後讀/修改物件
Elapsed: 00:00:00.02
SQL> update alltest02
  2  set object_id = 1
  3  /
update alltest02
       *
ERROR at line 1:
ORA-12838: 無法在並行模式下修改之後讀/修改物件
Elapsed: 00:00:00.01
SQL> insert into alltest02 select * from allobjects;
insert into alltest02 select * from allobjects
            *
ERROR at line 1:
ORA-12838: 無法在並行模式下修改之後讀/修改物件
Elapsed: 00:00:00.52
在另一個會話中執行select 語句。查詢結果是表中記錄為零，因為這個會話
看到的是insert append 以前的資料。
SQL> select count(*) from alltest02;
  COUNT(*)
----------
         0
二、使用insert append 方式插入資料的時候，將會使用HWM之上的空白塊，
如果要插入的表中HWM以下有很多沒有使用的空間的話，將會導致
儲存空間的浪費，所以說小資料量的插入沒有必要使用insert append 方式，
插入的表最好空的。有一種說法是如果要插入的表格是空白的，或者已經被
truncate 過,使用傳統的insert 和insert append 插入資料的方式hwm 是相同的
但是我個人認為是不相同的insert append 方式還是需要消耗更多的儲存空間。
因為在insert append 方式下hwm 推進的時候，總有一部分空間不能夠利用。
SQL>  analyze table alltest01 estimate statistics;
Table analyzed.
Elapsed: 00:00:11.71
SQL> column segment_name format a20
SQL>  select segment_name,blocks,bytes/1024 as "Size[KB]"
  2   from user_segments
  3   where segment_name = 'ALLTEST01';
SEGMENT_NAME             BLOCKS   Size[KB]
-------------------- ---------- ----------
ALLTEST01                 48128     385024
Elapsed: 00:00:00.07
SQL> select empty_blocks
  2  from user_tables
  3  where table_name = 'ALLTEST01';
EMPTY_BLOCKS
------------
         179
Elapsed: 00:00:00.12
SQL> analyze table alltest02 estimate statistics;
Table analyzed.
Elapsed: 00:00:09.36
SQL> select segment_name,blocks,bytes/1024 as "Size[KB]"
  2  from user_segments
  3  where segment_name = 'ALLTEST02';
SEGMENT_NAME             BLOCKS   Size[KB]
-------------------- ---------- ----------
ALLTEST02                 49152     393216
Elapsed: 00:00:00.01
SQL>  select empty_blocks
  2   from user_tables
  3   where table_name = 'ALLTEST02';
EMPTY_BLOCKS
------------
        1012
Elapsed: 00:00:00.00
alltest01 的hwm = 48128  - 179 - 1 =47948
alltest02 的 hwm = 49152  - 1012 -1 = 48139
可見使用insert append 方式插入資料的表alltest02 需要更多的儲存空間。
三、如果插入的表中存在外來鍵約束，將會使用傳統的insert 方式。
下面先來構造一個外來鍵存在的情況。
SQL> create table emp_01 as select employee_id,first_name,last_name
  2  from hr.employees;
Table created.
Elapsed: 00:00:00.12
SQL> alter table emp_01 add constraint emp_01_pk primary key (employee_id);
Table altered.
Elapsed: 00:00:00.59
SQL> create table emp_02 as select employee_id,department_id,salary
  2  from hr.employees;
Table created.
Elapsed: 00:00:00.08
SQL> alter table emp_02 add constraint emp_02_fk foreign key (employee_id)
  2  references emp_01 (employee_id);
Table altered.
Elapsed: 00:00:00.52
使用insert append 方式來插入資料，結果插入完成以後可以查詢，說明有外來鍵存在的
情況下insert append 方式將會自動轉化為傳統的insert 方式。如果在這種情況下確實
想要使用isnert append 方式，可以先disable constraint。
SQL> insert /*+append*/ into emp_02 select employee_id,department_id,salary
  2  from hr.employees;
107 rows created.
Elapsed: 00:00:00.12
SQL> select count(*) from emp_02;
  COUNT(*)
----------
       214
Elapsed: 00:00:00.07
四、並不是所以的表都可以使用insert append 方式插入資料，比如不支援物件型別，
堆組織表等。
小結：使用insert append 雖然可以帶來插入資料效能上面的優勢，但是也存在諸多的
缺點和限制，需要綜合全面考慮。建議在插入大資料量和剛建立的表或者truncate 以後
的表中使用。

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

相關文章