Oracle-L: Re: Are too many Foreign Keys in one table bad?

From: Mark Richard <mrichard_at_transurban.com.au>
Date: Mon, 06 Jan 2003 18:23:44 -0800
Message-ID: <F001.0052820F.20030106182344@fatcity.com>

All,

Point well taken (both Rachel's and Jared's). I should have said (and was even thinking - although the brain and hands sometimes act independently) "might not be worth indexing". It sounds like a helpdesk system for a pretty small customer base so I was assuming that system load isn't likely to be a problem. My experience has always been that if the fact is 1000 rows and the reference are maybe 3 - 10 then Oracle is going to eat it up for lunch no matter how it's structured unless a large number of concurrent user come along.

Now on a more serious note, when is the week-long Rachel Carmichael, Dan Fink, Jonathan Lewis, Connor, Jared, Kirti, et al "How to well and truly beat Oracle into Submission" seminar coming down under to Australia? I need to know so that I can start selling my soul to raise enough money to attend... With our dollar the way it is a seminar like that would cost about the same as my house.

                                                                                                                   
                    Rachel                                                                                         
                    Carmichael           To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>       
                    <wisernet100_at_y       cc:                                                                       
                    ahoo.com>            Subject:     Re: Are too many Foreign Keys in one table bad?              
                    Sent by:                                                                                       
                    root_at_fatcity.c                                                                                 
                    om                                                                                             
                                                                                                                   
                                                                                                                   
                    07/01/2003                                                                                     
                    12:33                                                                                          
                    Please respond                                                                                 
                    to ORACLE-L

Mark,

Based on the presentation and testing Dan Fink did for the last NYOUG meeting, it's possible that the ref tables SHOULD be indexed, and that it will help performance to index them.

Rachel

Mark Richard <mrichard_at_transurban.com.au> wrote:
> 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_at_work To: Multiple recipients
> of list ORACLE-L <ORACLE-L_at_fatcity.com>
> brain.com> cc:
>
> Sent by: Subject: Are too many
> Foreign Keys in one table bad?
> root_at_fatcity.c
>
> 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: GNorris2_at_workbrain.com
>
> 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: ListGuru_at_fatcity.com (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: mrichard_at_transurban.com.au
>
> 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: ListGuru_at_fatcity.com (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).
>

Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rachel Carmichael
  INET: wisernet100_at_yahoo.com

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: ListGuru_at_fatcity.com (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: mrichard_at_transurban.com.au

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: ListGuru_at_fatcity.com (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).

Received on Mon Jan 06 2003 - 20:23:44 CST