Is direct-path insert a good way to do bulk inserts in Oracle?

Question

We're trying to figure out the best way to handle BULK INSERTs using Oracle (10gR2), and I'm finding that it can be a pretty complicated subject. One method that I've found involves using the Append optimizer hint:

INSERT /*+ Append*/
INTO some_table (a, b)
VALUES (1, 2)

My understanding is that this will tell Oracle to ignore indexes and just put the results at the end of the table. Then, all I should have to do is rebuild the indexes:

ALTER INDEX some_index REBUILD

This would be easier than trying to launch SQL*Loader as an external process or doing some pl/SQL. This almost seems too easy. Is there something I'm missing? Any things that could come back to bite me if I take this approach?

Answer 1

A few notes ...

1. A single row cannot be appended, therefore APPEND is only valid with INSERT INTO ... SELECT FROM syntax.
2. An append is the addition of data above the high water mark of the table, in which the data is formatted into complete blocks that are then written to the table and which bypass the SQL engine
3. An append in parallel mode requires that each parallel query thread allocate at least one new extent to the table, into which the new blocks are written. This can be wasteful of space.
4. The indexes are not ignored, but maintenance of them is defered until the blocks have been written into the table.

See he docs for more important information: http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#ADMIN01509