do you need COMMIT; after INSERT /*+ append */

Question

I have COMMIT after every INSERT statement.

I am wondering if I need to COMMIT; after INSERT /*+ append */ ...

Answer 1

You probably don't want to commit after every INSERT, but you probably do want to commit after every INSERT /*+ APPEND */.

For a conventional INSERT, you probably only want to commit when the application's logical transaction is complete. Unnecessarily committing after every conventional insert would cause problems with performance and atomicity.

For a direct-path INSERT /*+ APPEND */, you probably need to commit as soon as possible. Direct-path writes improve performance at the expense of extra locking (and other problems). That extra locking blocks every session, even the current session, from even reading the object until the transaction is committed. To avoid the error ORA-12838: cannot read/modify an object after modifying it in parallel, it is common for programs to commit after every INSERT /*+ APPEND */.