Bulk insert into partitioned table and table level lock Bulk insert into partitioned table and table level lock oracle oracle

Bulk insert into partitioned table and table level lock


Your premise is slightly wrong. A direct-path insert does not lock the entire table if you use the partition extension clause.

Session 1:

insert /*+append */ into fg_test partition (p2)select * from fg_test where col >=1000;

Session 2:

alter table fg_test truncate partition p1;--table truncated

The new question is: When the partition extension clause is NOT used, why do conventional and direct-path inserts have different locking mechanisms? This clarification makes the question easier, but without inside knowledge the answer below is still only a guess.


It was easier to code a feature that locks the entire table. And it runs faster, since there is no need to track which partitions are updated.

There's usually no need for a more fine-grained lock. Most systems or processes that use direct-path writes only update one large table at a time. If a more fine-grained lock is really needed, the partition extension clause can be used. It's not quite as convenient, since only one partition can be referenced at a time. But it's good enough 99.9% of the time.


I found the follwing answer on asktom.oracle.com:

Ask Tom: Inserts with APPEND Hint

Tom explains many of the inner workings, but the reason why Oracle locks the whole table and not only affected partitions is still not clear.

Maybe it's just a design decision (e.g. not wanting the big bulky direct load to be potentially blocked by one smallish uncommited transaction and therefore locking all partitions ...)