Re: Index and Foreign key

willjamu_at_mindspring.com (James Williams) wrote in message news:<3eac76c6.53752522_at_nntp.mindspring.com>...
> On Sat, 26 Apr 2003 08:01:58 +0000 (UTC), "Salim Harouat"
> <salimharouat_at_aol.com> wrote:
>
> It is important to index all foreign key columns as a general rule
> even though 9i adds some features to migitate the effects (ora-0060's)
> of a parent causing the entire child table to be locked for the
> duration of the operation.
>
> >Hi Everybody,
> >
> > I wish to optimize certain queries done on a Oracle database to gain
> >in response times. With this intention, I plan to pose a non unique
> >index on each foreign key of the base. Am i right ?
> >
> >Thank u for everything
> >

Salim, please do not cross-post.

[Quoted] James, my experience says that the majority of FK constaints do not need an index to support them since they point to relatively static tables. Unless row deletes take place or updates on the key there will never be a need for the index to support locking. Probably only about 20% of FK constaints actually need an index to support efficient usage.

There have been several threads on this topic in the past.

IMHO -- Mark D Powell -- Received on Mon Apr 28 2003 - 15:56:40 CEST