[原创]append_values hint

oracle 11gR2 新出来个新提示 append_values ,他的作用是对于insert values方式可以直接路径插入, 可以减少日志量生成.在9i,10g只有insert into xx select 方式可以通过添加提示append来进行直接路径插入,但是对于insert into values的方式插入添加append是没有作用的。11R1的时候insert values添加append可以起到直接路径插入作用(insert select方式一样生效),11gR2又继续推出这个新参数了来防止人们混淆,相应的insert /*+ append*/ values的方式到11gR2对于直接路径插入又失效了,当然这个新参数依然是要结合大数据量批量加载数据的方式才可以取得理想的效果,对于每次单行插入的方式没有什么好处.原理上都是要搞成并行批量的方式直接在高水位线之上插入数据.所以需要在insert完成之后立刻提交,不提交的话再次进行相应表的事物操作会报错的.

下面简单测试一下:测试环境为oracle11R2

  这种方式可以有效减少日志量

1

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as noap
 
SQL>
SQL> drop TABLE TEST_OBJECTS_INSERT PURGE;
 
Table dropped
SQL> drop TABLE TEST_OBJECTS PURGE;
 
Table dropped
SQL> CREATE TABLE TEST_OBJECTS AS SELECT owner,object_name,object_id FROM dba_objects;
 
Table created
SQL> CREATE TABLE TEST_OBJECTS_INSERT AS SELECT owner,object_name,object_id FROM dba_objects WHERE 1=2;
 
Table created
 
SQL> set timing on
SQL>
SQL>  BEGIN
  2     FOR I IN (SELECT * FROM TEST_OBJECTS) LOOP
  3       EXECUTE IMMEDIATE 'INSERT INTO TEST_OBJECTS_INSERT VALUES(:1,:2,:3)'
  4         USING I.OWNER, I.OBJECT_NAME, I.OBJECT_ID;
  5     END LOOP;
  6     COMMIT;
  7   END;
  8 
  9  /
 
PL/SQL procedure successfully completed
 
Executed in 42.438 seconds
 
SQL>
SQL>    SELECT A.NAME, B.VALUE
  2     FROM V$MYSTAT B, V$STATNAME A
  3    WHERE A.STATISTIC# = B.STATISTIC#
  4      AND A.NAME LIKE '%redo size%';
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         214693328
redo size for lost write detection                                        0
redo size for direct writes                                            7488
 
Executed in 0.047 seconds
 
SQL>

2

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as noap
 
SQL> SET TIMING ON
SQL>
SQL>  DECLARE
  2 
  3     TYPE T_FORALL_TEST_TAB IS TABLE OF TEST_OBJECTS%ROWTYPE INDEX BY BINARY_INTEGER;
  4     L_TAB   T_FORALL_TEST_TAB;
  5     V_COUNT INTEGER := 0;
  6   BEGIN
  7     FOR I IN (SELECT * FROM TEST_OBJECTS) LOOP
  8       V_COUNT := V_COUNT + 1;
  9       L_TAB(V_COUNT).OWNER := I.OWNER;
 10       L_TAB(V_COUNT).OBJECT_NAME := I.OBJECT_NAME;
 11       L_TAB(V_COUNT).OBJECT_ID := I.OBJECT_ID;
 12     END LOOP;
 13     COMMIT;
 14 
 15     --CREATE TABLE TEST_OBJECT_1 AS SELECT OWNER,OBJECT_NAME,OBJECT_ID FROM DBA_OBJECTS;
 16     -- CREATE TABLE TEST_OBJECT_TTX AS SELECT OWNER,OBJECT_NAME,OBJECT_ID FROM DBA_OBJECTS WHERE 1=2;
 17     FORALL I IN L_TAB.FIRST .. L_TAB.LAST
 18       INSERT /*+ APPEND_VALUES(T)*/ INTO TEST_OBJECTS_INSERT T VALUES(L_TAB(I).OWNER,L_TAB(I).OBJECT_NAME,L_TAB(I).OBJECT_ID);
 19     COMMIT;
 20 
 21   END;
 22  /
 
PL/SQL procedure successfully completed
 
Executed in 4.39 seconds
 
SQL>
SQL>    SELECT A.NAME, B.VALUE
  2     FROM V$MYSTAT B, V$STATNAME A
  3    WHERE A.STATISTIC# = B.STATISTIC#
  4      AND A.NAME LIKE '%redo size%';
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                            132656
redo size for lost write detection                                        0
redo size for direct writes                                            7332
 
Executed in 0.047 seconds

3

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as noap
 
SQL> SET TIMING ON
SQL>
SQL>  DECLARE
  2 
  3     TYPE T_FORALL_TEST_TAB IS TABLE OF TEST_OBJECTS%ROWTYPE INDEX BY BINARY_INTEGER;
  4     L_TAB   T_FORALL_TEST_TAB;
  5     V_COUNT INTEGER := 0;
  6   BEGIN
  7     FOR I IN (SELECT * FROM TEST_OBJECTS) LOOP
  8       V_COUNT := V_COUNT + 1;
  9       L_TAB(V_COUNT).OWNER := I.OWNER;
 10       L_TAB(V_COUNT).OBJECT_NAME := I.OBJECT_NAME;
 11       L_TAB(V_COUNT).OBJECT_ID := I.OBJECT_ID;
 12     END LOOP;
 13     COMMIT;
 14 
 15     --CREATE TABLE TEST_OBJECT_1 AS SELECT OWNER,OBJECT_NAME,OBJECT_ID FROM DBA_OBJECTS;
 16     -- CREATE TABLE TEST_OBJECT_TTX AS SELECT OWNER,OBJECT_NAME,OBJECT_ID FROM DBA_OBJECTS WHERE 1=2;
 17     FORALL I IN L_TAB.FIRST .. L_TAB.LAST
 18       INSERT /*+ APPEND_VALUES(T)*/ INTO TEST_OBJECTS_INSERT T VALUES(L_TAB(I).OWNER,L_TAB(I).OBJECT_NAME,L_TAB(I).OBJECT_ID);
 19     COMMIT;
 20 
 21   END;
 22  /
 
PL/SQL procedure successfully completed
 
Executed in 4.39 seconds
 
SQL>
SQL>    SELECT A.NAME, B.VALUE
  2     FROM V$MYSTAT B, V$STATNAME A
  3    WHERE A.STATISTIC# = B.STATISTIC#
  4      AND A.NAME LIKE '%redo size%';
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                            132656
redo size for lost write detection                                        0
redo size for direct writes                                            7332
 
Executed in 0.047 seconds
 
SQL>   DECLARE
  2 
  3     TYPE T_FORALL_TEST_TAB IS TABLE OF TEST_OBJECTS%ROWTYPE INDEX BY BINARY_INTEGER;
  4     L_TAB   T_FORALL_TEST_TAB;
  5     V_COUNT INTEGER := 0;
  6   BEGIN
  7     FOR I IN (SELECT * FROM TEST_OBJECTS) LOOP
  8       V_COUNT := V_COUNT + 1;
  9       L_TAB(V_COUNT).OWNER := I.OWNER;
 10       L_TAB(V_COUNT).OBJECT_NAME := I.OBJECT_NAME;
 11       L_TAB(V_COUNT).OBJECT_ID := I.OBJECT_ID;
 12     END LOOP;
 13     COMMIT;
 14 
 15     --CREATE TABLE TEST_OBJECT_1 AS SELECT OWNER,OBJECT_NAME,OBJECT_ID FROM DBA_OBJECTS;
 16     -- CREATE TABLE TEST_OBJECT_TTX AS SELECT OWNER,OBJECT_NAME,OBJECT_ID FROM DBA_OBJECTS WHERE 1=2;
 17     FORALL I IN L_TAB.FIRST .. L_TAB.LAST
 18       INSERT /*+ APPEND */ INTO TEST_OBJECTS_INSERT T VALUES(L_TAB(I).OWNER,L_TAB(I).OBJECT_NAME,L_TAB(I).OBJECT_ID);
 19     COMMIT;
 20 
 21   END;
 22  /
 
PL/SQL procedure successfully completed
 
Executed in 3.813 seconds
 
SQL>
SQL>    SELECT A.NAME, B.VALUE
  2     FROM V$MYSTAT B, V$STATNAME A
  3    WHERE A.STATISTIC# = B.STATISTIC#
  4      AND A.NAME LIKE '%redo size%';
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                          36994652
redo size for lost write detection                                        0
redo size for direct writes                                               0
 
Executed in 0.047 seconds
 
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as noap
 
SQL>
SQL>  DECLARE
  2 
  3     TYPE T_FORALL_TEST_TAB IS TABLE OF TEST_OBJECTS%ROWTYPE INDEX BY BINARY_INTEGER;
  4     L_TAB   T_FORALL_TEST_TAB;
  5     V_COUNT INTEGER := 0;
  6   BEGIN
  7     FOR I IN (SELECT * FROM TEST_OBJECTS) LOOP
  8       V_COUNT := V_COUNT + 1;
  9       L_TAB(V_COUNT).OWNER := I.OWNER;
 10       L_TAB(V_COUNT).OBJECT_NAME := I.OBJECT_NAME;
 11       L_TAB(V_COUNT).OBJECT_ID := I.OBJECT_ID;
 12     END LOOP;
 13     COMMIT;
 14 
 15     --CREATE TABLE TEST_OBJECT_1 AS SELECT OWNER,OBJECT_NAME,OBJECT_ID FROM DBA_OBJECTS;
 16     -- CREATE TABLE TEST_OBJECT_TTX AS SELECT OWNER,OBJECT_NAME,OBJECT_ID FROM DBA_OBJECTS WHERE 1=2;
 17     FORALL I IN L_TAB.FIRST .. L_TAB.LAST
 18       INSERT
 19       INTO TEST_OBJECTS_INSERT T
 20       VALUES
 21         (L_TAB(I).OWNER, L_TAB(I).OBJECT_NAME, L_TAB(I).OBJECT_ID);
 22     COMMIT;
 23 
 24   END;
 25  /
 
PL/SQL procedure successfully completed
 Executed in 3.75 seconds
SQL>
SQL>    SELECT A.NAME, B.VALUE
  2     FROM V$MYSTAT B, V$STATNAME A
  3    WHERE A.STATISTIC# = B.STATISTIC#
  4      AND A.NAME LIKE '%redo size%';
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                          36982448
redo size for lost write detection                                        0
redo size for direct writes                                               0
 
5

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as noap
 
SQL> alter TABLE TEST_OBJECTS_INSERT NOLOGGING;
SQL>  DECLARE
  2 
  3     TYPE T_FORALL_TEST_TAB IS TABLE OF TEST_OBJECTS%ROWTYPE INDEX BY BINARY_INTEGER;
  4     L_TAB   T_FORALL_TEST_TAB;
  5     V_COUNT INTEGER := 0;
  6   BEGIN
  7     FOR I IN (SELECT * FROM TEST_OBJECTS) LOOP
  8       V_COUNT := V_COUNT + 1;
  9       L_TAB(V_COUNT).OWNER := I.OWNER;
 10       L_TAB(V_COUNT).OBJECT_NAME := I.OBJECT_NAME;
 11       L_TAB(V_COUNT).OBJECT_ID := I.OBJECT_ID;
 12     END LOOP;
 13     COMMIT;
 14 
 15     --CREATE TABLE TEST_OBJECT_1 AS SELECT OWNER,OBJECT_NAME,OBJECT_ID FROM DBA_OBJECTS;
 16     -- CREATE TABLE TEST_OBJECT_TTX AS SELECT OWNER,OBJECT_NAME,OBJECT_ID FROM DBA_OBJECTS WHERE 1=2;
 17     FORALL I IN L_TAB.FIRST .. L_TAB.LAST
 18       INSERT
 19       INTO TEST_OBJECTS_INSERT T
 20       VALUES
 21         (L_TAB(I).OWNER, L_TAB(I).OBJECT_NAME, L_TAB(I).OBJECT_ID);
 22     COMMIT;
 23 
 24   END;
 25  /
 
PL/SQL procedure successfully completed

Executed in 3.875 seconds
 
SQL> select * from redo_size_v;
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                          36976400
redo size for lost write detection                                        0
redo size for direct writes                                               0
 
SQL>

insert into values ：

time: 42.438   redo_size:214693328

insert/*+ append_values(x)*/ into table_name x values(collect) :

time: 4.39    redo_size:132656

insert/*+ append */ into table_name  values(collect) :

time: 3.813    redo_size:36994652

insert into table_name  values(collect) :

time: 3.75    redo_size:36982448

noarchive mode + table is nologging + insert into table_name value(collect)

time: 3.875   redo_size:36976400

可见日志量明显减少