Direct insert
Date: Fri, 16 Dec 2011 20:20:29 +0000 (UTC)
Message-ID: <pan.2011.12.16.20.20.29_at_gmail.com>
I've seen many RTFM replies given to people because of trying something like this:
INSERT /*+ APPEND */ INTO EMP VALUES (....) This type of thing used to guarantee a condescending reply from people who knew that the APPEND hint only works with sub-query, not with the VALUES clause. Well, as Bob Dylan sings, things are changing:
This is a link on 11.2 SQL reference. The following description is interesting:
APPEND_VALUES Hint
Description of append_values_hint.gif follows
Description of the illustration append_values_hint.gif
The APPEND_VALUES hint instructs the optimizer to use direct-path INSERT with the VALUES clause. If you do not specify this hint, then conventional INSERT is used.
In direct-path INSERT, data is appended to the end of the table, rather than using existing space currently allocated to the table. As a result, direct-path INSERT can be considerably faster than conventional INSERT.
The APPEND_VALUES hint can be used to greatly enhance performance. Some examples of its uses are:
In an Oracle Call Interface (OCI) program, when using large array binds or array binds with row callbacks
In PL/SQL, when loading a large number of rows with a FORALL loop that has an INSERT statement with a VALUES clause
The APPEND_VALUES hint is only supported with the VALUES clause of the INSERT statement. If you specify the APPEND_VALUES hint with the subquery syntax of the INSERT statement, it is ignored and conventional insert will be used. To use direct-path INSERT with a subquery, refer to "APPEND Hint".
This is also an interesting hint, which enables me to ignore unique key violations and continue with the insert:
IGNORE_ROW_ON_DUPKEY_INDEX Hint
Description of ignore_row_on_dupkey_index.gif follows
Description of the illustration ignore_row_on_dupkey_index.gif
Note:
The CHANGE_DUPKEY_ERROR_INDEX, IGNORE_ROW_ON_DUPKEY_INDEX, and
RETRY_ON_ROW_CHANGE hints are unlike other hints in that they have a
semantic effect. The general philosophy explained in "Hints" does not
apply for these three hints.
The IGNORE_ROW_ON_DUPKEY_INDEX hint applies only to single-table INSERT operations. It is not supported for UPDATE, DELETE, MERGE, or multitable insert operations. IGNORE_ROW_ON_DUPKEY_INDEX causes the statement to ignore a unique key violation for a specified set of columns or for a specified index. When a unique key violation is encountered, a row-level rollback occurs and execution resumes with the next input row. If you specify this hint when inserting data with DML error logging enabled, then the unique key violation is not logged and does not cause statement termination.
The semantic effect of this hint results in error messages if specific rules are violated:
If you specify index, then the index must exist and be unique. Otherwise, the statement causes ORA-38913.
You must specify exactly one index. If you specify no index, then the statement causes ORA-38912. If you specify more than one index, then the statement causes ORA-38915.
You can specify either a CHANGE_DUPKEY_ERROR_INDEX or IGNORE_ROW_ON_DUPKEY_INDEX hint in an INSERT statement, but not both. If you specify both, then the statement causes ORA-38915.
As with all hints, a syntax error in the hint causes it to be silently ignored. The result will be that ORA-00001 will be caused, just as if no hint were used.
Oracle 11.2 is in breaking from the old lore in many, many ways.
-- http://mgogala.byethost5.comReceived on Fri Dec 16 2011 - 14:20:29 CST