Database Reference
In-Depth Information
comma-separated list. This function takes three arguments:
A
,
B
,
C
. If argument
A
is not null, then it returns argument
B
; otherwise, it returns argument
C
. This query assumes that the owner of the constraint is the owner of the table and
index as well. If another user indexed the table or the table is in another schema (both rare events), it will not work
correctly.
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:
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 have an
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:
•
When you query from the parent to the child. Consider the
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
).
•
You do not join from the parent to the child (like
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.
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.
