关于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.
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.
