Oct 23 2009
Hints for Direct-path Insert Statements
Up to Oracle Database 10g Release 2, direct-path inserts are supported only by INSERT INTO … SELECT … statements (including multitable inserts), MERGE statements (for the part inserting data), and applications using the OCI direct-path interface (for example, the SQL*Loader utility). At the statement level two methods are available to specify that a direct-path insert has to be used:
- Specify the APPEND hint in the SQL statement
- Execute the SQL statement (actually, at least the INSERT part) in parallel
Let’s have a look to an example. Notice that:
- The APPEND hint is used to execute a direct-path insert.
- The APPEND hint does not work with a “regular” INSERT statement that uses the VALUES clause.
- To check whether the direct-path insert is performed, the modified table is queried without committing (or rolling back) the transaction. As a result, after a direct-path insert the database engine raises an ORA-12838.
SQL> SELECT * FROM v$version WHERE rownum = 1;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
SQL> CREATE TABLE t (n NUMBER);
SQL> INSERT /*+ append */ INTO t SELECT 1 FROM dual;
SQL> SELECT * FROM t;
SELECT * FROM t
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
SQL> COMMIT;
SQL> INSERT /*+ append */ INTO t VALUES (2);
SQL> SELECT * FROM t;
N
----------
1
2
Strangely enough, at least for me, in Oracle Database 11g Release 1 the behavior of the APPEND hint has changed. In fact, it is accepted also for a “regular” INSERT statement that uses the VALUES clause. Let’s run the same test as before to illustrate the new behavior.
SQL> SELECT * FROM v$version WHERE rownum = 1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
SQL> CREATE TABLE t (n NUMBER);
SQL> INSERT /*+ append */ INTO t SELECT 1 FROM dual;
SQL> SELECT * FROM t;
SELECT * FROM t
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
SQL> COMMIT;
SQL> INSERT /*+ append */ INTO t VALUES (1);
SQL> SELECT * FROM t;
SELECT * FROM t
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
Even more strange, in Oracle Database 11g Release 2 the behavior of the APPEND hint was reverted to the pre-11g one! But, since the feature is really useful in some situations, a new hint called APPEND_VALUES is available. The following example illustrates the new behavior.
SQL> SELECT * FROM v$version WHERE rownum = 1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
SQL> CREATE TABLE t (n NUMBER);
SQL> INSERT /*+ append */ INTO t SELECT 1 FROM dual;
SQL> SELECT * FROM t;
SELECT * FROM t
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
SQL> COMMIT;
SQL> INSERT /*+ append */ INTO t VALUES (2);
SQL> SELECT * FROM t;
N
----------
1
2
SQL> COMMIT;
SQL> INSERT /*+ append_values */ INTO t VALUES (3);
SQL> SELECT * FROM t;
SELECT * FROM t
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel