Re: To foreign key or not to foreign key
- From: Tim Hall <timhall1@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 16 Dec 2004 08:31:06 -0800
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@xxxxxxxxx> 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@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.
> >
> > --
> > //www.freelists.org/webpage/oracle-l
> >
>
>
> --
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
