oracleindexingfull-table-scantable-lock

Oracle Index - full table scan/lock


Found this here:

In general, consider creating an index on a column in any of the following situations:

I don't understand why a full table lock would occurr in such situation. I would've thought that if I tried to delete/update the primary key in the parent table that a full table scan would be performed on the child table.

Where does the lock come from?


Solution

  • Have a look at this Tom Kyte blog entry. In it, he refers to the Oracle documentation, where this explanation is offered:

    In the first scenario, if the column is not indexed, the entire table must be locked because Oracle does not know which rows must be updated in the child table. With an index, Oracle can identify the rows in question and just lock them. Without the full table lock, it would be possible to modify the parent and have another session modify the child to something that violated the constraint.