Locking and Issues - Oracle Database Transactions and Locking Revealed - page 25

The prior script also checks to see if the index type is a B*Tree index ( NORMAL or NORMAL/REV ). We're checking to
see if it's a B*Tree index because a bitmap index on a foreign key column does not prevent the locking issue.

■ in data warehouse environments, it's common to create bitmap indexes on a fact table's foreign key columns.
however, in data warehouse environments, usually the loading of data is done in an orderly manner through scheduled
etL processes and, therefore, would not encounter the situation of inserting into a child table as one process while
concurrently deleting from a parent table from another process (like you might encounter in an oLtp application).

Note

So, the prior script shows that table C has a foreign key on the column X but no index. By creating a B*Tree index
on X , we can remove this locking issue all together. In addition to this table lock, an unindexed foreign key can also be
problematic in the following cases:

• When you have an

ON DELETE CASCADE and have not indexed the child table. For example,
EMP is child of DEPT. DELETE DEPTNO = 10 should CASCADE to EMP . If DEPTNO in EMP is not
indexed, you will get a full table scan of EMP for each row deleted from the DEPT table. This full
scan is probably undesirable, and if you delete many rows from the parent table, the child
table will be scanned once for each parent row deleted.

• When you query from the parent to the child. Consider the

EMP/DEPT example again. It is very
common to query the EMP table in the context of a DEPTNO . If you frequently run the following
query (say, to generate a report), you'll find that not having the index in place will slow down
the queries:

select * from dept, emp
where emp.deptno = dept.deptno and dept.deptno = :X;
When do you not need to index a foreign key? The answer is, in general, when the following conditions are met:

• You do not delete from the parent table.

• You do not update the parent table's unique/primary key value (watch for unintended updates

to the primary key by tools).

DEPT to EMP ).
If you satisfy all three conditions, feel free to skip the index; it's not needed. If you meet any of the preceding
conditions, be aware of the consequences. This is the one rare instance when Oracle tends to overlock data.

• You do not join from the parent to the child (like

Lock Escalation
When lock escalation occurs, the system is decreasing the granularity of your locks. An example would be the
database system turning your 100 row-level locks against a table into a single table-level lock. You are now using one
lock to lock everything and, typically, you are also locking a whole lot more data than you were before. Lock escalation
is used frequently in databases that consider a lock to be a scarce resource and overhead to be avoided.

■ Note oracle will never escalate a lock. never.