I don't think you should give up a foreign key constraint for a table that
contains only 5, no matter the table is a big one or not. For your table,
its foreign key constraint will only affect the INSERTs and UPDATEs
sentences, not the SELECTs or DELETEs.
In fact, it is the accesses to the tables containing the referenced columns
that should be considered more, especially UPDATEs and DELETEs. When they
happen, Oracle has to ensure the FK constraint won't be violated, which
could be time consuming. Estimating the frequence of such event is quite
important. If there are frequent changes on the referenced columns
(referenced tables), you should consider create indexes on the same columns
forming the foreign keys (the referencing table) to help Oracle not to have
to serial scan the referencing table. This might not be clear, so here is an
example:
Suppose we have a society responsible of keeping track of every human birth
on Earth. So we'll have a table named PEOPLE, which will have, among others,
a column named SEX. To ensure integrity of this columns, let's have a table
named HUMANSEX. Assuming there are only two different values for the SEX
attribute and assuming there won't be a lot of changes in the possible
values for it, there is no problem creating a foreign key constraint for
this column in the PEOPLE table.
However, suppose genetic experts set up a new possible value for SEX every
hour. Our society will probably add these values to the HUMANSEX table, thus
increasing the necessary work to register a new birth (insert into the
PEOPLE table). Moreover, suppose politicians decide to forbid values of SEX,
invented months ago by genetic experts (scientists always evolve faster than
politicians). So, our society will have to delete entries from the HUMANSEX
table, forcing Oracle to ensure there are no record in the PEOPLE table with
this value of the SEX attribute. This is in that case I suggest to create an
index on the PEOPLE.SEX column. (Can anybody confirm this to the community?)
Finally, I might be a purist, but there should be a big performance
degradation to force me giving up a foreign key constraint. These
constraints help keeping data integrity, which programs application could
not ensure at 100%: programs always have bugs, Oracle doesn't ;-).
Finally finally, everytime I post in this newsgroup (rarely) I want to make
it short... and everytime I fail!
Michel Gauthier
Donohue Inc.
Reza <reza_at_digital-dispatch.co.uk> wrote in message
news:939902191.2877.0.nnrp-04.d4f07aaa_at_news.demon.co.uk...
> Hi
> I just read your article. This not a problem for oracle as long as you can
> afford to index all these columns.
> I have got a table with 8-10 Million records with all these FKs indexed
and
> it performs reasonbaly fast of course on a 4 CPU HP-UX!
> Reza
> Sanaa BELBAGRI <belbagri_at_ispalliance.net> wrote in message
> news:37FA4893.37E8F0CA_at_ispalliance.net...
> > Hi all,
> >
> > I am implementing a database using ORACLE and I have a problem.
> > One of my tables contain 5 foreign keys moreover it's supposed to be one
> > of the biggest and most accesible table in the application.
> > So I wonder what can I do to avoid the problem of a very bad performance
> > in term of access to the table (for example withdraw the foreign keys
> > and treat the problem on the programming side by testing everytime the
> > data entred,....etc).
> > Can you help?
> >
> > Many Thanks
> >
> >
> >
> >
> >
>
>
Received on Thu Oct 14 1999 - 09:00:30 CDT
