Re: To foreign key or not to foreign key

One factor to consider is whether you are using a tool like Oracle Forms to do the updates --

When a record in a base-table block is updated in Oracle Forms, one of two things happens (depending on block-property settings for the block in question):
1. All database fields (including PK/FKs) are included in the UPDATE statement, even if their values did not change, or 2. Only those fields whose values actually changed are included in the UPDATE statement.

#1 (which is the default setting for the property) can be a major pain with FKs in place, because (last I checked, either 9.0 or 9.2, I forget) Oracle will still kick off the FK revalidation even if the value in the PK column did not change! So if your block is based on EMP and you change an employee's salary (or whatever), Oracle Forms includes the Employee# (PK) in the UPDATE statement, and every column in the database that has a FK pointing to this Employee# column gets pointlessly revalidated (even though the employee# in EMP did not get a new value), leading to locking and performance issues in some cases when the constrained columns are not indexed. #2 isn't a very attractive alternative, since it can be rather SGA-unfriendly if the form doesn't update the same list of column(s) each time.

Hope this helps - actually I hope you don't even have to deal with this particular aspect of the problem :) Tim

On Tue, 14 Dec 2004 11:02:38 -0800, Jared Still <jkstill_at_gmail.com> wrote:
> 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
>

--
http://www.freelists.org/webpage/oracle-l