CODEWITHSUNDEEP
oracle-databaseindexingfull-table-scantable-lock
Robotronx
Robotronx

Reputation: 1818

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?

Upvotes: 2

Views: 2698

Answers (1)

DCookie
DCookie

Reputation: 43533

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

  • Prevents a full table lock on the child table. Instead, the database acquires a row lock on the index.
  • Removes the need for a full table scan of the child table. As an illustration, assume that a user removes the record for department 10 from the departments table. If employees.department_id is not indexed, then the database must scan employees to see if any employees exist in department 10.

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.

Upvotes: 5

Related Questions