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?

[This hint is explained in the documentation](http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements006.htm#sthref495). Do you have more than one process inserting at the same time, or uncommitted data in the table? — Alex Poole, May 23 '14 at 07:14
@AlexPoole Yes, Multiple threads are accessing the table at the same time — Mithun Khatri, May 23 '14 at 07:15
[The answer might be here](https://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:1211797200346279484). And if you want to make sure is it the one that making the program hang, you may try the query without adding it (`INSERT into table..` instead of `INSERT /*+APPEND*/ into table`) . — Baby, May 23 '14 at 07:16
You need to do `COMMIT;` whenever you run the command using the `/*+ APPEND */` hint. It may only "look" hung, like if you try to view the data in the table after running the command, because the `INSERT` hasn't been committed yet. You'll get the error `ORA-12838: cannot read/modify an object after modifying it in parallel` if you don't. If that's what you are seeing, that's what you have to do to get around it. — vapcguy, Jul 22 '16 at 19:36

score 8 · Accepted Answer · edited Sep 04 '18 at 12:31

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.

"This hint only works when you use INSERT as SELECT statement". Are you sure it works for only this case? — Mithun Khatri, May 23 '14 at 09:14
I think it is like that. 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. — ibre5041, May 23 '14 at 10:06
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"." — ibre5041, May 23 '14 at 10:08
The APPEND hint will only reduce redo generation if the table[space] is set to NOLOGGING or DB is in NOARCHIVELOG mode. http://www.oracle-base.com/articles/misc/append-hint.php — user584583, Aug 04 '14 at 13:16

score 4 · Answer 2 · answered May 23 '14 at 07:17

insert typically looks for the first empty space in your table to add the new record. While this conserves space, it may sometime slow down the operation.

/*+APPEND*/ is a hint which causes the insert statement to always, well, for lack of a better term, append the newly inserted row at the end of the table. This may waste some space, but is usually faster. It's especially useful if you know you don't have too many empty regions in the middle of the table (i.e., you don't perform a lot of deletes and updates on it).

The APPEND hint will only reduce redo generation if the table[space] is set to NOLOGGING or DB is in NOARCHIVELOG mode. oracle-base.com/articles/misc/append-hint.php — user584583, Aug 04 '14 at 13:17

score 4 · Answer 3 · answered Mar 30 '18 at 10:45

/*+ APPEND */

called direct path insert.
Data are appended beyond high water mark(HWM) - ignoring table's free space map, no triggers are fired and no constraints are checked.
Only one session can use it on particular table at the same time

If you want to check the hint being used then find that particular session and its 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

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.

score 2 · Answer 4 · answered May 23 '14 at 07:18

It is a ORACLE compliler hint. It is there for a purpose and is not a comment. This hint is used there to speed up insertions, so I don't think it is the cause for hanging up the program.

However, please ask your dba to check available free space in the tablespace this table is using.(Your dba will understand this statement better :) )

There might be a issue that there is very less space available for any more insertions in that tablespace, which the dba should be able to resolve.

Why there could be less space available? because the APPEND hint wastes space as explained by above answer by @Mureinik, and can be a problem if there are too many frequent insertions with this hint in that table.

score 0 · Answer 5 · edited May 23 '17 at 11:46

I don't know about "speeding up" the insert, but I've definitely found the hint, and yes, this is what Oracle calls it - not a comment - extremely useful for maintaining the order of records when I do an insert from one table into another, when I pair the command with an ORDER BY at the end:

INSERT /*+ append */ INTO MYAPP.COUNTRIES (ID, CODE, NAME)
SELECT ID, CODE, NAME FROM MYAPP.OLD_COUNTRIES_TABLE ORDER BY NAME ASC;
COMMIT;

I've gotten flack that this is just a coincidence (see this thread on S.O.), but I've used it many times over, now, and that's at least the fruit I'm ending up with. If you use INSERT with ORDER BY, but without the append hint, the ORDER BY gets ignored and is utterly useless for ensuring the records can then be retrieved according to the order in which they were inserted (see my proof in my answer, here).

Why INSERT /*+APPEND*/ is used?

5 Answers5

Why INSERT /+APPEND/ is used?