How to solve the FK's w/o Matching Indexes (or an unusable one) (# tables)

Toad for Oracle
1

Message from: Ed D’Agostini

Hi gurus. Can you please help me understand and fix this issue? FK’s w/o
Matching Indexes (or an unusable one) Regards, Ed

Historical Messages

Author: Edson D’Agostini
Date: Tue Jan 10 16:22:56 PST 2012
Hi gurus. Can you please help me understand and fix this issue? FK’s w/o
Matching Indexes (or an unusable one) Regards, Ed
__

2

Morning Ed,

Message from: Ed D'Agostini
Hi gurus. Can you please help me understand and fix this issue? FK's w/o
Matching Indexes (or an unusable one) Regards, Ed

Basically it's like this, if you have a parent table and a child table
related by an FK constraint, then you should have an index on the CHILD
table's FK column(s) if, at any time, the rows in the PARENT table can be:

  • Deleted.
  • Updated - in the column(s) referenced by the child table's FK.
  • Used in a query where there is a join using the FK column(s).

Why is it a problem? Well, when you decide to delete a parent row,
Oracle needs to check if there are child rows related, and if so, either
NULL the FK columns, or Cascade Delete the child rows or prevent the
parent from being deleted.

So, it has to find at least one, or all the rows in the child table that
match the soon to be deleted parent. To do this without an index will
require a full scan of the child.

However, before the scan takes place, Oracle requires an exclusive lock
on the child table - so, first it must wait until no other process is
using the child table, than exclusively lock it preventing any other
process from using it (except in read mode) then run the scan.

If it finds child rows that match, it can then continue processing
according to the cascade rules on the FK constraint - NULLing out,
Cascade Deleting or preventing the parent rows from being deleted. Then,
the exclusive lock is released.

All of this prevents other work from taking place on the related tables

  • and don't forget, the child table itself may be the parent of another
    table (or tables) so the situation can get very slow very quickly as
    things cascade down the relationship.

You could just always index any table's FK column(s) but remember, each
index adds overhead to processing times plus, they also require space to
store the index data.

The best solution is to know what is allowed to be done to the rows in
the parent table and index accordingly.

Toad has a feature whereby it can find all the unindexed FK tables and
alert you to the ones that might need to be looked at. USe it wisely -
don't just index everything that is reported, look at how the tables are
used and index accordingly.

HTH

--
Cheers,
Norm. [TeamT]

3

Hi Norm.

Sorry to take so long to get back to you.

I really appreciated the way you explained it to me. Thank you very very much.

Regards,

Ed