Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

/*+ APPEND */ hint and concurrency

BEDEMay 6 2020 — edited May 6 2020

So, /*+ APPEND */ is used after insert in case of an insert select to make Oracle insert the records above the high watermark, not looking into the freelists for that table in order to reclaim empty blocks.

What happens for the latest Oracle Database versions in the event when one session performs such an insert /*+ APPEND */ into table_x and does not commit and other sessions try to insert rows in the same table also with /*+ APPEND */ or without that APPEND hint?

I recall in Oracle 11.2, one developer wrote two successive insert /*+ APPEND */ into the same table without any commit and then Oracle barfed some error. I have then changed that replacing the two insert select statements with one single insert select with append using union all. Now, on SQL live I see there is no problem if in the same session are found two inserts with APPEND hint.

Still, I don't know how this will play in case of concurrency. I am asking this seeing that right now I have only Oracle 12.1 available where I am working, so that I can't test on a newer version.

This post has been answered by Mike Kutz on May 6 2020
Jump to Answer
Comments
Post Details
Added on May 6 2020
6 comments
2,281 views