An /*+ append */ hint, when added to an insertion, tells Oracle you’d like to do a direct path insert instead of a conventional path insert.  Most people use the append hint to speed up an insert; however, direct path inserts aren’t always faster than conventional inserts.

You use an append hint like this:

You can tell whether or not the hint “worked” by trying to select from the table that you just inserted into before you commit.  If you get a regular result set back, then you know you didn’t perform a direct-path insert.  If you get an error message like the following:

ORA-12838: cannot read/modify an object after modifying it in parallel
12838. 00000 –  “cannot read/modify an object after modifying it in parallel”
*Cause:    Within the same transaction, an attempt was made to add read or
modification statements on a table after it had been modified in parallel
or with direct load. This is not permitted.
*Action:   Rewrite the transaction, or break it up into two transactions
one containing the initial modification and the second containing the
parallel modification operation.

Then you know your append hint “worked.”

Why do you get an error instead of rows?  Tables that have been inserted into via direct path insert all the data beyond the high water mark of a table.  If you’re in the middle of a transaction, Oracle is supposed to show you the table with whatever changes you’ve made to it.  Well, there’s a hard and fast rule that says that the database engine can’t read past the high-water mark of a table, and until you commit the transaction, all the data you’ve inserted lies after the high water mark.  Committing the transaction moves the high water mark of a table beyond the data you’ve inserted.

Since all the data gets inserted past the high water mark, a direct-path insert will not attempt to “fill in” any holes in the table caused by previous deletions.  So if you have a table that is constantly being deleted from and inserted into via direct path, the amount of space allocated to the table will continue to grow, even if the number of rows remains constant.

When performing a regular, conventional-path insert, only a row-level lock must be acquired.

However, in order to perform an direct path insert, an exclusive lock must be held on the table…so you’ll want to be cautious about using append inserts in a OLTP database so that you’re not causing blocking.

One of the main reasons direct path inserts are considered to be faster than conventional-path inserts is because the database doesn’t create “undo” for the table like it does for a conventional path insert.

This isn’t to say that “zero” undo is used in a direct-path insertion.  Certain things, such as update to the database dictionary still require undo to be logged.  But you can see it’s significantly less.

Notice that I said that undo isn’t generated for the table.  Undo is still generated for indexes on the table–adding an index to the table increases the amount of undo generated.

Of course, the amount of undo generated can have an effect on the amount of redo generated, so the append hint can help with that, too.

So you can save quite a bit of I/O bandwidth by using the append hint.

One of the cool things about the append hint is it builds off “mini-indexes” off to the side that get merged into the real indexes when the transaction is committed.  So when you insert into an indexed table using conventional path insert, Oracle has to find the index block where a value belongs and insert it.  If the block is full, it can do one of two different types of inserts–either a 50/50 block split or a 90/10 block split.  Normally, a 50/50 block split is performed, where a single block is split into two separate blocks…half of the values go into block 1, the other half go into block2.  If, however, the value that needs to be inserted into the block falls at the *very end* of the list (keeping in mind that indexes are ordered by column values…and then, subsequently by rowid), then a 90/10 block split can be performed, where 90% of the values are in 1 block, and 10% of the values fall into another block.  If your data isn’t ordered very well, using a conventional-path insert can cause indexes to grow unnecessarily from the amount of 50/50 block splits that are performed.

With direct-path insertion, though, the mini-indexes that are built ensure that the data gets inserted into the index in order, meaning we get more 90/10 block splits and our indexes are smaller (meaning index access is more efficient).

A direct path load may not always be faster than a conventional-path insertion.  Using direct path load bypasses the database buffer cache.  So if you’re performing an insertion from a small table that’s likely already in your database buffer cache, a conventional path insert may actually be faster.

You can identify a direct-path insertion in an explain plan by the words “LOAD AS SELECT” while conventional loads are identified in explain plans by “LOAD TABLE CONVENTIONAL”

Parallel insertions are always done as direct path.

Note the emphasis on the word insertions above.  Parallel selections that are part of an insert can totally be conventional.  In other words, if we move the parallel hint from right after INSERT to right after SELECT, we get something entirely different:

Here, the parallel is on the selection, but we’re still doing a conventional path insertion (side note, usually it makes sense, if you’re going to perform a parallel insertion to go ahead and parallelize your selection as well).

A direct-path hint will be ignored if it is illegal.  For example, index-organized tables don’t allow you to write to them the same way that a heap-organized table would–the rows must go in a very specific order in the table, so there’s no real way to write past the high-water mark in that instance, so all insertions into an IOT are conventional path.

Likewise, if the table has a foreign key constraint on it, direct path insertions are ignored.

I’ve been told by a source that the APPEND hint doesn’t work with an INSERT…VALUES statement.  Right now, I’m using Oracle 11.1.0.6.0 Enterprise Edition, and the append hint seems to work just fine for me.

I know that starting in 11r2, a special APPEND_VALUES hint has been created for this particular instance, although you may want to carefully consider when you would use something like this.  For example, consider a process that loads rows one at a time into a table.  If a direct-path insertion is performed, that means firstly, that you must commit after every row you insert before the database will let you insert another row (commits are expensive and can weigh heavily on your database when done in excess).  Besides that, remember that append means that it will write below the high water mark, meaning that a new block will be allocated for every insertion, and when the row is committed, the high water mark is moved so the next insert will allocate another block…meaning a new block will be allocated for each row inserted!  So depending on how wide your table is (how many bytes are in each row), you may end up with several table blocks that are mostly empty besides one row, which makes for inefficient I/O and a waste of space.