Re: How many FKs is too many ?

xtanto wrote:

> Hi Oracle gurus,
>
> I have two tables, one with 100.000 rows and the other with about 4
> million rows. Each table has about 55 columns, with 15 of them refer
> to a reference Table.
>
> If I put FK constraint on these 15 columns (mean I have 15 FKs).
> Is this too many ? will this cause poor performance ?
> Is there any best practices for this ?
>

The answer, like so many other things in Oracle, is "it depends".

For a well designed system, 15 FKs might not be significant and IMO 15 is not 'too many'. That's assuming a good design. Jumping from design to implementation requires testing - full load!

You say "15 of them refer to a reference table" ... hopefully not the same reference table as that would give me concern about the design or implementation.

Every constraint will have some performance impact, but there are many, many other things that can, and will, cause poor performance. I'd start with the full set of FKs and turn them off if necessary.

Given that a database also must provide reliability, as well as performance, you need to balance the performance that you measure in your fully-loaded test system with the credibility your system will have when data is [apparently] wrong due to violated constraints.

As a general guide to best practises, start with a good review of Thomas Kyte's book "Effective Oracle by Design".

/Hans Received on Thu Oct 21 2004 - 09:23:16 CDT