關於insert /*+ append*/ 各種insert插入速度比較

SQL> select count(*) from t;
COUNT(*)
----------
5442048
****************************
SQL> alter table t1 nologging;
SQL> insert /*+append*/ into t1
2 select * from t;
5442048 rows created.
Elapsed: 00:00:55.73
****************************
SQL> create table t1 nologging parallel(degree 2) as select * from t;
Table created.
Elapsed: 00:00:56.79
********************************
SQL> insert /*+APPEND */ into t1
2 select /*+ parallel(t,4) */ * from t;
5442048 rows created.
Elapsed: 00:01:38.39
***********************************
alter table t1 nologging;
SQL> insert into t1
2 select * from t;
5442048 rows created.
Elapsed: 00:01:46.18
***********************************

SQL> alter table t1 logging;
Table altered.
SQL> insert into t1
2 select * from t;
5442048 rows created.
Elapsed: 00:02:03.51
*********************************
SQL> create table t1
2 as
3 select * from t;
Table created.
Elapsed: 00:03:03.34

insert 和 insert /*+append*/對hwm的影響
(1) insert

[email=sys@ORCL%3Ecreate]sys@ORCL>create[/email] table t_hwmtablespace hwm as select * from all_objects;
表已建立。

[email=sys@ORCL%3Eselect]sys@ORCL>select[/email]count(*) from t_hwm;
COUNT(*)
----------
50176
已選擇 1 行。

[email=sys@ORCL]sys@ORCL[/email]>
[email=sys@ORCL%3Edelete]sys@ORCL>delete[/email] from t_hwm;
已刪除50176行。

[email=sys@ORCL%3Eexec]sys@ORCL>exec[/email]show_space('t_hwm','auto')
Total Blocks............................5120
Total Bytes.............................41943040
Unused Blocks...........................4429
Unused Bytes............................36282368
Last Used Ext FileId....................10
Last Used Ext BlockId...................5129
Last Used Block.........................691
PL/SQL 過程已成功完成。

[email=sys@ORCL%3Einsert]sys@ORCL>insert[/email]into t_hwm select * from all_objects where rownum < 2000;
已建立1999行。

[email=sys@ORCL%3Eexec]sys@ORCL>exec[/email]show_space('t_hwm','auto')
Total Blocks............................5120
Total Bytes.............................41943040
Unused Blocks...........................4429
Unused Bytes............................36282368
Last Used Ext FileId....................10
Last Used Ext BlockId...................5129
Last Used Block.........................691
--在插入HWM下資料量時，不會修還HWM
PL/SQL 過程已成功完成。

[email=sys@ORCL%3Einsert]sys@ORCL>insert[/email]into t_hwm select * from all_objects ;
已建立50176行。

[email=sys@ORCL%3Eexec]sys@ORCL>exec[/email]show_space('t_hwm','auto')
Total Blocks............................5120
Total Bytes.............................41943040
Unused Blocks...........................4374
Unused Bytes............................35831808
Last Used Ext FileId....................10
Last Used Ext BlockId...................5129
Last Used Block.........................746
PL/SQL 過程已成功完成。

--在插入大量資料量時，超過HWM時增加HWM
(2) insert /*+ append */ into
[email=sys@ORCL%3Edelete]sys@ORCL>delete[/email]from t_hwm;
已刪除52175行。

[email=sys@ORCL%3Eexec]sys@ORCL>exec[/email]show_space('t_hwm','auto')
Total Blocks............................5120
Total Bytes.............................41943040
Unused Blocks...........................4374
Unused Bytes............................35831808
Last Used Ext FileId....................10
Last Used Ext BlockId...................5129
Last Used Block.........................746
PL/SQL 過程已成功完成。

[email=sys@ORCL%3Einsert]sys@ORCL>insert[/email]/*+ append */ into t_hwm select * from all_objects where rownum
< 2000;
已建立1999行。

[email=sys@ORCL%3Eexec]sys@ORCL>exec[/email]show_space('t_hwm','auto')
Total Blocks............................5120
Total Bytes.............................41943040
Unused Blocks...........................4374
Unused Bytes............................35831808
Last Used Ext FileId....................10
Last Used Ext BlockId...................5129
Last Used Block.........................746
PL/SQL 過程已成功完成。

[email=sys@ORCL%3Einsert]sys@ORCL>insert[/email]/*+ append */ into t_hwm select * from all_objects where rownum
< 20000;
[email=sys@ORCL%3Ecommit]sys@ORCL>commit[/email];
提交完成。
[email=sys@ORCL%3Einsert]sys@ORCL>insert[/email]/*+ append */ into t_hwm select * from all_objects where rownum
< 20000;
已建立19999行。
[email=sys@ORCL%3Eexec]sys@ORCL>exec[/email]show_space('t_hwm','auto')
Total Blocks............................5120
Total Bytes.............................41943040
Unused Blocks...........................4349
Unused Bytes............................35627008
Last Used Ext FileId....................10
Last Used Ext BlockId...................5129
Last Used Block.........................771
PL/SQL 過程已成功完成。


1. append方式新增記錄對insert into ... values語句不起作用。
2. 以append方式批次插入的記錄，其儲存位置在hwm 之上，即使hwm之下存在空閒塊也不能使用。
3. 以append方式插入記錄後，要執行commit，才能對錶進行查詢。否則會出現錯誤：
ORA-12838: 無法在並行模式下修改之後讀/修改物件
4. 在歸檔模式下，要把表設定為nologging，然後以append方式批次新增記錄，才會顯著減少redo數量。在非歸檔模式下，不必設定表的 nologging屬性，即可減少redo數量。如果表上有索引，則append方式批次新增記錄，不會減少索引上產生的redo數量，索引上的redo 數量可能比表的redo數量還要大
5. 同 nologging 配合會更快的,使用新的BLOCK 而不使用FREELIST中的塊 增加插入速度,使用這個hint可以將資料使用直接路徑插入到表的高水線之後，由於是連續的沒有使用的空間，所以插入速度快。就是說直接插入,減少了搜尋塊的時間.


會在一定程度上造成空間浪費
請看oracle文擋中的描述：
APPEND
The APPEND hint lets you enable direct-path INSERT if your database is runningin serial mode. Your database is in serial mode if you are not using EnterpriseEdition. Conventional INSERT is the default in serial mode, and direct-pathINSERT is the default in parallel mode.
In direct-path INSERT, data is appended tothe end of the table, rather than using existing space currently allocated tothe table. As a result, direct-path INSERT can be considerably faster than conventionalINSERT.

SQL語句中的最佳化提示
APPEND : Only valid for INSERT .. SELECT. Allows INSERT to work like directload or to perform parallel insert.