APPEND_VALUES hint

A coworker of mine just brought a new hint to my attention – the APPEND_VALUES hint.  I’m used to the append hint which makes insert statements use direct path instead of conventional loading.  Here is what the old standby append looks like:

insert /*+append */ into target select * from source;

Here is what the new append_values hint looks like:

insert /*+append_values */ into target values (‘some data’);

It looks like this in new in 11.2.  I tried my tests on an 11.1 instance and got different results.  I got an error on 11.2 trying to use a regular for loop:

SQL> 
SQL> BEGIN
  2  
  3    FOR k IN 1..10 LOOP
  4      FOR j IN 1..10000 LOOP
  5        INSERT /*+append_values */ INTO test VALUES (1);
  6      END LOOP;
  7      commit;
  8    END LOOP;
  9  END;
 10  /
BEGIN
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after 
modifying it in parallel
ORA-06512: at line 5

But I don’t get this error on 11.1.  Also, on my 11.2 instance my test ran 14 seconds without the hint and 10 seconds with it, but on 11.1 and 10.2 I saw almost no difference.

Here is the 11.2 test with the hint:

SQL> DECLARE
  2    TYPE NumList IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  3    testlist NumList;
  4  BEGIN
  5  
  6    FOR j IN 1..10000 LOOP
  7      testlist(j) := j;
  8    END LOOP;
  9  
 10    FOR k IN 1..1000 LOOP
 11  
 12      FORALL i IN 1..10000
 13        INSERT /*+ append_values */
 14        INTO test
 15        VALUES (testlist(i));
 16  
 17      commit;
 18  
 19    END LOOP;
 20  
 21  END;
 22  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.01

Here is the 11.2 test without the hint:

SQL> DECLARE
  2    TYPE NumList IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  3    testlist NumList;
  4  BEGIN
  5  
  6    FOR j IN 1..10000 LOOP
  7      testlist(j) := j;
  8    END LOOP;
  9  
 10    FOR k IN 1..1000 LOOP
 11  
 12      FORALL i IN 1..10000
 13        INSERT
 14        INTO test
 15        VALUES (testlist(i));
 16  
 17      commit;
 18  
 19    END LOOP;
 20  
 21  END;
 22  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:14.01

My two test scripts and their logs are in a zip here which you can download and try yourself.  I’m not sure if it is possible to construct an example with more dramatic improvement in performance but 10 seconds versus 14 is still worthwhile.

– Bobby