关于加快INSERT语句执行速度和HINT /*+ append */及/*+ append nologging */的使用

mahan

（非归档模式下）创建表T01：

SQL> create table t01 as select * from dba_objects where 1=2;

Table created.



（非归档模式下）查看当前redo大小：

SQL> select value

2 from v$mystat,v$statname

3 where v$mystat.statistic#=v$statname.statistic#

4 and v$statname.name='redo size'

5 /

VALUE

----------

49784



（非归档模式下）普通INSERT语句插入：

SQL> insert into t01 select * from dba_objects;

11698 rows created.

（非归档模式下）查看普通INSERT语句执行后，当前redo大小：

SQL> select value

from v$mystat,v$statname

2 3 where v$mystat.statistic#=v$statname.statistic#

4 and v$statname.name='redo size';

VALUE

----------

1305724



（非归档模式）采用HINT /*+ append*/执行INSERT语句：

SQL> insert /*+ append */ into t01 select * from dba_objects;

11698 rows created.



（非归档模式）查询带/*+ append*/的INSERT执行后，当前redo大小：

SQL> select value

2 from v$mystat,v$statname

3 where v$mystat.statistic#=v$statname.statistic#

4 and v$statname.name='redo size';

VALUE

----------

1308140

（非归档模式）计算使用HINT(/*+ append*/)和普通INSERT语句分别产生的redo大小：

SQL> select (1308140-1305724) redo_append,(1305724-49784) redo from dual;

REDO_APPEND REDO

----------- ----------

2416 1255940

可见，在非归档模式下，INSERT语句采用HINT /*+ APPEND*/的方式确实比普通INSERT语句产生的redo要少很多。

=========================================



下面测试归档模式下

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/arch

Oldest online log sequence 109

Next log sequence to archive 111

Current log sequence 111



（归档模式）查看当前redo大小：

SQL> select value

2 from v$mystat,v$statname

3 where v$mystat.statistic#=v$statname.statistic#

4 and v$statname.name='redo size'

5 /

VALUE

----------

19540

（归档模式）执行普通INSERT语句：

SQL> insert into t01 select * from dba_objects;

11698 rows created.

（归档模式）普通INSERT方式产生的redo大小：

SQL> select value

2 from v$mystat,v$statname

3 where v$mystat.statistic#=v$statname.statistic#

4 and v$statname.name='redo size'

5 /

VALUE

----------

1218884



（归档模式）采用HINT /*+ append*/执行INSERT语句：

SQL> insert /*+ append */ into t01 select * from dba_objects;

11698 rows created.

（归档模式）查询带/*+ append*/的INSERT执行后，当前redo大小：

SQL> select value

2 from v$mystat,v$statname

3 where v$mystat.statistic#=v$statname.statistic#

4 and v$statname.name='redo size';

VALUE

----------

2451664



（归档模式）计算使用HINT(/*+ append*/)和普通INSERT语句分别产生的redo大小：

SQL> select (2451664-1218884) redo_append,(1218884-19540) from dual;

REDO_APPEND (1218884-19540)

----------- ---------------

1232780 1199344

可见，在归档模式下，采用HINT与普通INSERT方式产生的redo量是相当的，且略大于普通INSERT语句产生的redo量。

=========================================



再看一种情况，归档模式，T01也是LOGGING模式

SQL> select value

2 from v$mystat,v$statname

3 where v$mystat.statistic#=v$statname.statistic#

4 and v$statname.name='redo size'

5 /

VALUE

----------

2541780

SQL> insert /*+ append nologging */ into t01 select * from dba_objects;

或者执行：

SQL> insert /*+ append */ into t01 nologging select * from dba_objects;

也将产生redo的量为：1233040。

=========================================



最后一种情况：

（归档模式）修改T01的属性为NOLOGGING：

SQL> alter table t01 nologging;

Table altered.

SQL> select table_name,logging from dba_tables where table_name='T01';

TABLE_NAME LOG

------------------------------ ---

T01 NO



（归档模式）查看当前redo值：

SQL> select value

2 from v$mystat,v$statname

3 where v$mystat.statistic#=v$statname.statistic#

4 and v$statname.name='redo size'

5 /

VALUE

----------

5010600



（归档模式）执行普通INSERT语句：

SQL> insert into t01 select * from dba_objects;

11698 rows created.

SQL> select value

2 from v$mystat,v$statname

3 where v$mystat.statistic#=v$statname.statistic#

4 and v$statname.name='redo size'

5 /

VALUE

----------

6209848



（归档模式）采用HINT /*+ append*/执行INSERT语句：

SQL> insert /*+ append */ into t01 select * from dba_objects;

11698 rows created.

SQL> select value

2 from v$mystat,v$statname

3 where v$mystat.statistic#=v$statname.statistic#

4 and v$statname.name='redo size'

5 /

VALUE

----------

6210536



（归档模式）计算两者产生的redo差值：

SQL> select (6210536-6209848) redo_append,(6209848-5010600) redo from dual;

REDO_APPEND REDO

----------- ----------

688 1199248

可见，在归档模式下，将表调整为NOLOGGING，产生的redo量是最少的！



对比一下：

非归档，T01(LOGGING)，普通INSERT语句产生的redo值：1255940

非归档，T01(LOGGING)，HINT /*+append*/，产生的redo值：2416

归档，T01(LOGGING)，普通INSERT语句产生的redo值：1199344

归档，T01(LOGGING)， HINT /*+ append*/，产生的redo值：1232780

归档，T01(LOGGING)，/*+append*/ + nologging，产生的redo值：1233040

归档，T01(NOLOGGING)，普通INSERT语句产生的redo值：688

归档，T01(NOLOGGING)，HINT /*+append*/，产生的redo值：1199248

综合以上的数据，可以明白，如果想INSERT语句执行的更快，产生更少的redo，分两种情况：

非归档模式：

1、在INSERT语句中使用HINT /*+ append */或者使用HINT /*+ append nologging */两者插入数据的速度相同，但是后者产生的日志要少10倍左右

2、insert /*+ append */堆数据时，要排队，其它同样加了append的session只能等待，所以要小心使用。



归档模式：

1、将目标表修改为NOLOGGING（alter table xxx nologging），然后在INSERT语句中使用HINT /*+ append */.或者HINT /*+ append nologging */

2、append在没有使用nologging的情况下，并不会减少归档日志的产生。

3、将表改为nologging属性的情况下插入数据，几乎不会产生归档日志，但前提是必须要使用append参数，否则产生依然会产生归档日志，只不过表在创建时不会产生归档。