Re: index on foreign key

In article <139473e4.4b2b2923_at_usw-ex0106-047.remarq.com>,   mcomer <mcomer2NOmcSPAM_at_hotmail.com.invalid> wrote:
> In an Oracle database, should all foreign key columns have
> indexes?
>
> Marc
>
> * Sent from RemarQ http://www.remarq.com The Internet's Discussion
 Network *
> The fastest and easiest way to search and participate in Usenet -
 Free!
>
>

Most, not all.

See

http://osi.oracle.com/~tkyte/unindex/index.html

for a script to detect unindexed foreign keys and pointers into the documentation to a locking issue
(which is real -- table locks can arise if you delete a parent record or update the primary key of a parent record and have unindexed foreign keys. IMHO, update a primary key NEVER happens so its a non-issue. Deletion of a parent in a parent child requires almost an index on the child not only to avoid the table lock but to avoid the full scan of child that would happen).

In addition to the table lock issue that might hit you, an unindexed foreign key is bad in the following cases as well:

o 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. 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.

o 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:

    select * from dept, emp
     where emp.deptno = dept.deptno and dept.deptno = :X;

to generate a report or something, you'll find not having the index in place will slow down the queries.

So, when do you NOT need to index a foriegn key. In general when the following conditions are met:

o you do NOT delete from the parent table. (especially with delete cascade -- it is a double whammy)

o you do NOT update the parent tables unique/primary key value.

o you do NOT join from the PARENT to the CHILD (like DEPT->EMP).

If you satisfy all three above, feel free to skip the index, it is not needed. If you do any of the above, be aware of the consequences.

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.