Bulk insert into partitioned table and table level lock

Question

I want to know the main reason (the mechanics of the segments, blocks, locks that the engine makes) why a massive insert (with a direct path) locks the whole table, so if I insert into a section, I cannot trim another section that is not affected (by apparently) insert.

Regular insertion (without the hint of adding) allows you to trim some unaffected partitions. (Please note that I am talking about a transaction without obligation.)

Below is an example of his illustration.

Let there be a table:

CREATE TABLE FG_TEST (COL NUMBER ) PARTITION BY RANGE (COL) (PARTITION "P1" VALUES LESS THAN (1000), PARTITION "P2" VALUES LESS THAN (2000)); Insert into table fg_test values (1); insert into table fg_test values (1000); commit; 

Session 1:

 insert into table fg_test select * from fg_test where col >=1000; --1 rows inserted; 

Session 2:

 alter table fg_test truncate partition p1; --table truncated 

Session 1:

 rollback; insert /*+append */ into table fg_test select * from fg_test where col >=1000; --1 rows inserted; 

Session 2:

 alter table fg_test truncate partition p1; --this throws ORA-00054: resource busy and acquire with NOWAIT specified --or timeout expired 

The Doc on Diret-Path Insert is pretty cool on this subject and just says:

In the INSERT direct path, the database gets exclusive locks on the table (or in all partitions of the partitioned table). As a result, users cannot insert, update, or delete a table operation simultaneously and create and create parallel operation indices are not allowed.

How INSERT Direct-Path does not explain why locking is necessary for all partitions. And why doesn't plain paste block unaffected partitions? (My intuition is that locking is done at the block level)

Answer 1

Your premise is a bit wrong. Inserting a direct path does not block the entire table if you use the section 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 

A new question: when the section expansion proposal is NOT used, why do regular and direct inserts have different locking mechanisms? This clarification eases the question, but without internal knowledge, the answer below remains a hunch.

---

It was easier to code a function that locks the entire table. And it works faster since there is no need to keep track of which sections are being updated.

Normally there is no need for a finer grain block. Most systems or processes that use direct path entries only update one large table at a time. If a finer grained lock is required, you can use the partition extension clause. This is not entirely convenient, since only one section can be referenced at a time. But that's good enough 99.9% of the time.

Answer 2

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

Ask Tom: APPEND Tooltips

Tom explains many of the internal workings, but the reason Oracle locks the entire table, and not only the affected partitions, is not yet clear.

Maybe this is just a design decision (for example, not wanting the large bulky direct download to be potentially blocked by one small irregular transaction and, therefore, blocked all partitions ...)