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@xxxxxxxxx> 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

Other related posts: