Append Hint in Oracle

Question

I am inserting huge volume of records from one table into another table using insert into using select statement in package. I am using multithreading in select statement.Shall I use /*+ APPEND NOLOGGING */ hint in the insert statement. Is it really improve performance and also is it good idea to create index on global temporary table in oracle?

Answer 1

The performance improvements of the APPEND hint can be massive for multiple reasons. Enabling direct-path writes allows Oracle to avoid writing multiple copies of the data, such as redo, undo, and archive logs. Direct-path writes can also enable compression, automatic statistics collections, and other optimizations. But beware of the important downsides of direct-path writes: the changes are not recoverable until the next backup, and the table is completely locked until a COMMIT.

If you're already using multi-threading on the reads, you might as well use multi-threaded writes with a hint like INSERT /*+ APPEND PARALLEL(8) */ .... But you might need to use ENABLE_PARALLEL_DML hint or enable parallel DML at the session level.

There's a good chance the APPEND hint won't initially improve performance because there are many limitations on direct-path writes, such as no the logging property (if your database is in archivelog mode), triggers, foreign keys, etc. (Note that LOGGING is not a hint, it is an object property.)

Look carefully at your execution plans to ensure you are getting direct-path writes. You should see an operation named LOAD AS SELECT instead of LOAD TABLE CONVENTIONAL to ensure direct-path writes are used. And you should see a PX ... operation before any operation you want parallelized.

If you're using a modern version of Oracle, the Note section of the execution plan may tell you why you're not getting direct-path writes or parallelism. And a SQL Monitor Report (generated through DBMS_SQLTUNE.REPORT_SQL_MONITOR) can help you identify problems with the degree of parallelism and other performance problems.

On many systems, you can improve INSERT performance by 100X or more, but it may take a lot of effort.