Greg,
I don't think Oracle will have a real problem with 15 tables or 1,000 rows.
If the ref tables are quite small then they won't even be worth indexing -
Oracle will just read the entire table at one anyway. You might want to
tell Oracle to CACHE the reference tables, although I don't think you'll
see a performance gain really. Unfortunately I can't give any performance
suggestions because I am used to the other end of the scale (ie: 250
million rows in data)
You probably could store CODE in the main table, but if you are going to
need the description frequently then all benefit is lost anyway. Either
way though I'm sure that you'll have more problems getting the 15 joins
right when writing the queries than Oracle's CBO will have when looking at
the query - I've seen some real nasty queries get pushed into Oracle's
optimisor and as long at the statistics are valid then it does a pretty
good job.
Cheers,
Mark.
PS: Why would the reference CODE change instead of the DESCRIPTION? I'm
guessing the code will be meaningful such as "HIGH", "CRITICAL", etc and
description might be "Must fix within 1 hr". Even still, I think you are
right when you said that CODE isn't likely to change often, if at all.
Gregory Norris
<GNorris2@work To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
brain.com> cc:
Sent by: Subject: Are too many Foreign Keys in one
table bad?
[EMAIL PROTECTED]
om
07/01/2003
07:03
Please respond
to ORACLE-L
I am designing some tables to store Customer Support Data.
The main table (SUPPORT_DATA) contains many (up to 15) foreign key links to
other tables.
Most of the other tables are small lookup REFTABLES (eg Priority Type).
A few bigger tables store up to 1000 records eg CUSTOMER_DATA.
I am concerned that to get data for one Support record will involve a join
of 15 Tables and possibly more for reports, and that this many tables may
confuse the Cost Based Optimiser.
I am considering storing the CODE in the SUPPORT_DATA table instead of the
ID for the reference tables. This will reduce the number of joins greatly.
_____________________________________
Design Proposed
SUPPORT_DATA
Id (PK)
<reftable>_code (FK)
support_data_desc
....
<REFTABLE>
<reftable>_id (PK)
<reftable>_code (Unique Constraint)
<reftable>_description
_____________________________________
The Main problems I see with this are that DATA storage increases (I can
deal with that) and that I will have to create a trigger to update all
SUPPORT_DATA if one of the CODES in a REFTABLE is updated (this would be
rare and so not a great concern).
Is storing the CODE a sound option?
Any hints or comments would be appreciated =)
THX Greg
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Gregory Norris
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message
(or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
by reply e-mail or by telephone on (61 3) 9612-6999.
Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message
that do not relate to the official business of
Transurban City Link Ltd
shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mark Richard
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
