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:

http://tinyurl.com/7ydat39

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.com