Should foreign keys be indexed?

This excerpt is from Tom Kyte's new book Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions, published by Apress in September 2005. Tom Kyte is Vice President of the Core Technologies Group at Oracle and has been using Oracle since 1988. For more frequently asked questions and myths about Oracle indexes, click here.

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 updates that updates every column in the table, regardless of whether or not the value was UPDATE statement 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.
• 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 query to generate a report or something, you'll find not having the index in place will slow down the queries. 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 ...

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Oracle database performance problems and tuning Oracle tutorial library: SearchOracle.com's learning guides What managers should consider when starting a database scaling project Oracle releases new database, says 11g upgrade will cut costs Oracle raises prices on database management packs Oracle New Year's resolutions, part 1: Advice for navigating 2009 Solving common Oracle errors guide Oracle 11g data compression Varchar or number for better performance? Do statistics on SYS-owned objects hurt performance in 10g? Inside the Oracle 11g SQL Performance Advisor, part 1 Oracle tutorials, tips and FAQs Part 3: The final ERP transition: Crossing the finish line successfully Oracle tutorial library: SearchOracle.com's learning guides What managers should consider when starting a database scaling project Part 2: Maintaining your place in the race -- ERP project management Oracle virtualization introduction guide Revving your engines: Tuning up your ERP project plan Oracle Openworld 2009: Here's what to expect Oracle OpenWorld 2009 Special Report SearchOracle.com podcasts Do I need to buy an Oracle license for a development database? Oracle tutorials, tips and FAQs Research

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary 10g  (SearchOracle.com) delimiter  (SearchOracle.com) extent  (SearchOracle.com) field  (SearchOracle.com) flexfield  (SearchOracle.com) foreign key  (SearchOracle.com) Oracle  (SearchOracle.com) row  (SearchOracle.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

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.

As a side note, if you believe that a child table is getting locked via an unindexed foreign key and you would like to prove it (or just prevent it in general), you can issue the following:

Now, any UPDATE or DELETE to the parent table that would cause the table lock will receive

This is useful in tracking down the piece of code that is doing what you believe should not be done (no UPDATEs or DELETEs of the parent primary key), as the end users will immediately report this error back to you.

To see other frequently asked questions and myths about indexes, click here.