Re: To foreign key or not to foreign key

If the FK table is not updated frequently, then there really isn't an issue with having an index on it is there?

Personally, I index all FK. If the table is being updated, you must have it. If the table is not updated, then there is no issue with having an index.

Depends a bit on your workload too I guess. There's no way I would try to determine which FK tables need an index based on frequency of DML.

*) If you leave on off, it will come back later to haunt you. *) mentioned earlier, not enough time for that kind of analysis.

I played the game with an app last year where 900+ FK were not indexed, and indexed a few as needed. It was a waste of time IMO, and I finally just made sure all were indexed and be done with it. No more locking issues following that.

Ideal? no.

Practical? Certainly.

As the lone DBA here, idealism sometimes loses to practicality.

Except where RAID 5 is concerned. ;)

Jared

On Tue, 14 Dec 2004 08:33:52 -0800 (PST), Fuad Arshad <fuadar_at_yahoo.com> wrote:
> I know this has been discussed here before and i did find a couple of jonathan lewis's old posts
>
> The thing is we have a project where the consultants want to ensure about 10-15 foreign keys per tables to enforce parent child relationships.
> I've seen locking issues beforer and was wondering if the list could put down a some pros and cons as to going or not going with a primary foriegn key strategy.
> This is a oltp type system and sub second response is what the enpd product requires( isnt that the description of every project these days).
> The consultants want every foreign key indexed. which i think is way too much a performance degradation since inserts are going to be major part of the application.
>
> --
> http://www.freelists.org/webpage/oracle-l
>

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
--
http://www.freelists.org/webpage/oracle-l