Re: How many FKs is too many ?

DA Morgan <damorgan_at_x.washington.edu> wrote in message news:<1098424219.831695_at_yasure>...
> Joel Garry wrote:
>
> > krislioe_at_gmail.com (xtanto) wrote in message news:<e1c9bd55.0410210337.2957db19_at_posting.google.com>...
> >
> >>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 ?
> >>
> >>Thank you for your help,
> >>xtanto
> >
> >
> > I have a table with 4M rows, a primary key that has 25 columns on it,
> > with 4 other indices. It has very poor performance. But that's what
> > happens when you take a poor packaged design and make it worse with
> > customizations for business rules.
> >
> > So I can't change the design or get rid of the customizations. But I
> > can make it work (which appears to be more than the people who
> > originally did the customizations can say). It's a living. Not
> > something I'd recommend. I'd recommend what everyone else is saying,
> > fix the design. But it really depends on what you are doing with the
> > table. I'm certainly not doing OLTP on it! Meaning, define
> > performance. If a report runs overnight, so what? If a customer is
> > lost because "the computer is down," big problem!
> >
> > jg
> > --
> > @home.com is bogus.
> > What's in your database? http://www.securityfocus.com/news/9758
>
> Did you consider an IOT with OVERFLOW?

Don't want to stress that version of the product (which requires 8i and RBO) too much with things beyond it's ken. Will be converting it to 9i soon, so maybe then. I was leaning toward partitioning to "renormalize" it, hadn't considered IOT...[few minutes reviewing concepts]... don't think IOT can make up for design this bad. [few minutes in bug database]... latches, aiiiiy. Good suggestion, though, considering the lack of version info in thread, thanks.

Actually, I make the users extract subsets of data to work with (behind the scenes, so they could be using an instance elsewhere and have no clue, and the extract also corrects and cleans some data), because there is just no reason for them to be hitting this monster directly and repeatedly in competition with the OLTP. It should be off in a DW. 3/4 of it should be archived. All in good time.

jg

--
@home.com is bogus.
Still laughing that they would call something ass.awk.