Database Reference
In-Depth Information
Previously, I said that you can use to your advantage the fact that totally Null entries are not stored in a B*Tree
index—here is how. Say you have a table with a column that takes exactly two values. The values are very skewed; say,
90 percent or more of the rows take on one value and 10 percent or less take on the other value. You can index this
column efficiently to gain quick access to the minority rows. This comes in handy when you would like to use an index
to get to the minority rows, but you want to full scan to get to the majority rows, and you want to conserve space. The
solution is to use a Null for majority rows and whatever value you want for minority rows or, as demonstrated earlier,
use a function-based index to index only the non-null return values from a function.
Now that you know how a B*Tree will treat Null values, you can use that to your advantage and take precautions
with unique constraints on sets of columns that all allow Nulls (be prepared to have more than one row that is all Null
as a possibility in this case).
Should Foreign Keys Be Indexed?
The question of whether or not foreign keys should be indexed comes up frequently. We touched on this subject in
Chapter 6 when discussing deadlocks. There, I pointed out that unindexed foreign keys are the biggest single cause
of deadlocks that I encounter, due to the fact that an update to a parent table's primary key or the removal of a parent
record will place a table lock on the child table (no modifications to the child table will be allowed until the statement
completes). This locks many more rows than it should and decreases concurrency. I see it frequently when people
are using a tool that generates the SQL to modify a table. The tool generates an
UPDATE
statement that updates every
column in the table, regardless of whether or not the value was modified. This, in effect, updates the primary key
(even though they never changed the value). For example, Oracle Forms will do this by default, unless you tell it to
just send modified columns over to the database. In addition to the table lock issue that might hit you, an unindexed
foreign key is bad in the following cases as well:
•
When you have an
ON DELETE
CASCADE
and have not indexed the child table. For example,
EMP
is child of
DEPT. DELETE FROM DEPT WHERE DEPTNO = 10
should cascade to
EMP
. If
DEPTNO
in
EMP
is not indexed, you will get a full table scan of
EMP
. 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.
EMP
/
DEPT
example again. It is very
common to query the
EMP
table in the context of a
DEPTNO
. If you frequently query the following to
generate a report or something, you'll find 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.dname = :X;
This is the same argument I gave for indexing the
NESTED_COLUMN_ID
of a nested table in
Chapter 10. The hidden
NESTED_COLUMN_ID
of a nested table is nothing more than a foreign key.
So, when do you
not
need to index a foreign key? 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, either purposely or by
accident (via a tool).
You do
•
not
join from the parent table to the child table, or more generally, the foreign key
columns do not support an important access path to the child table and you do not use them
in predicates to select data from this table (such as
DEPT
to
EMP
).
If you satisfy all three criteria, feel free to skip the index—it is not needed and will slow down DML on the child
table. If you do any of the three, be aware of the consequences.
