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

下面這個表是根據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 以後

的表中使用。

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

相關文章