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

可見日誌量明顯減少

[原創]append_values hint

相關文章