Why INSERT /*+APPEND*/ is used?

Question

A java program is doing bulk insertion into the Oracle table. It works fine for 100-200 records but its hanging for more than 4000 records.

When I checked the query, its having INSERT /*+APPEND*/ in it.

What is INSERT /*+APPEND*/ and why is it used in INSERT queries? Is the program hanging because of this thing?

Answer 1

It's a SQL optimizer hint. In your case most likely it has NO impact. Maybe it's a premature optimization.

This hint should enforce so called direct path insert, which bypasses Oracle's buffer cache and writes data directly into data-files. Data are appended beyond high water mark(HWM) - ignoring table's free space map, no triggers are fired and no constraints are checked. On the other hand this type of insert is blocking. Only one session can use it on particular table at the same time.

An excerpt from docs:

"The APPEND hint is only supported with the subquery syntax of the INSERT statement, not the VALUES clause. If you specify the APPEND hint with the VALUES clause, it is ignored and conventional insert will be used. To use direct-path INSERT with the VALUES clause, refer to "APPEND_VALUES Hint" This hint only works when you use INSERT as SELECT statement

 insert into <table> SELECT * FROM ....

When you insert values Oracle silently ignores it. Newer Oracle versions also support APPEND_VALUES hint.

If you want to validate the hint being used open Toad or SQL Developer, select session browser, find that particular session and it's current SQL and exec plan. When you see in the exec plan something like "INSERT into TABLE CONVENTIONAL" then the hint is ignored. If you see "INSERT as SELECT" then you are using direct path load.