Re: Are too many Foreign Keys in one table bad?
If the code is unique its not a problem. Only part that can give trouble is u r allowing to update code in reftable . That's not coool . - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, January 06, 2003 12:03 PM > > 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) > _code (FK) >support_data_desc > > > > _id (PK) > _code (Unique Constraint) > _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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: BigP 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).
Re: Are too many Foreign Keys in one table bad?
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 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) _code (FK) support_data_desc _id (PK) _code (Unique Constraint) _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
Re: Are too many Foreign Keys in one table bad?
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 <[EMAIL PROTECTED]> 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 > > 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) > _code (FK) >support_data_desc > > > > _id (PK) > _code (Unique Constraint) > _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-Mai
Re: Are too many Foreign Keys in one table bad?
> 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.
Not necessarily. There can be quite a difference between using
an index on a small table, and not using one.
The following will illustrate:
#---
drop table fts1;
drop table fts2;
create table fts1 (
refcode varchar2 (6) not null
, description varchar2(30) not null
);
begin
for i in 1..100
loop
execute immediate
'insert into fts1 values('
|| || 'C' || i || || ','
|| || 'Code ' || i ||
|| ')';
end loop;
commit;
end;
/
create table fts2
as
select *
from fts1;
create index fts2_code_idx
on fts2(refcode);
analyze table fts1 compute statistics;
analyze table fts2 compute statistics;
#---
Now the test harness is run. This is based on Tom Kytes
run_stats test harness. http://asktom.oracle.com/~tkyte/runstats.html
#---
-- test_harness.sql
-- from Tom Kyte - asktom.oracle.com/~tkyte/runstats.html
-- see ~/oracle/dba/run_stats for all files
declare
l_start number;
--add any other variables you need here for the test...
begin
delete from run_stats;
commit;
-- start by getting a snapshot of the v$ tables
insert into run_stats select 'before', stats.* from stats;
-- and start timing...
l_start := dbms_utility.get_time;
-- for things that take a very small amount of time, I like to
-- loop over it time and time again, to measure something "big"
-- if what you are testing takes a long time, loop less or maybe
-- not at all
for i in 1 .. 1000
loop
-- your code here for approach #1
declare
r_code fts1%rowtype;
begin
select refcode, description into r_code
from fts1
where refcode = 'C25';
select refcode, description into r_code
from fts1
where refcode = 'C50';
select refcode, description into r_code
from fts1
where refcode = 'C75';
select refcode, description into r_code
from fts1
where refcode = 'C99';
end;
end loop;
dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs'
);
-- get another snapshot and start timing again...
insert into run_stats select 'after 1', stats.* from stats;
l_start := dbms_utility.get_time;
for i in 1 .. 1000
loop
-- your code here for approach #2
declare
r_code fts2%rowtype;
begin
select /*+ index(fts2, fts2_code_idx) */
refcode, description into r_code
from fts2
where refcode = 'C25';
select /*+ index(fts2, fts2_code_idx) */
refcode, description into r_code
from fts2
where refcode = 'C50';
select /*+ index(fts2, fts2_code_idx) */
refcode, description into r_code
from fts2
where refcode = 'C75';
select /*+ index(fts2, fts2_code_idx) */
refcode, description into r_code
from fts2
where refcode = 'C99';
end;
end loop;
dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs'
);
insert into run_stats select 'after 2', stats.* from stats;
end;
/
#---
The results:
17:56:17 rsysdevdb.radisys.com - jkstill@dv01 SQL> @th
57 hsecs
44 hsecs
PL/SQL procedure successfully completed.
The code using the index was only marginally faster. The
real savings are in resources not consumed when an index is used.
17:56:14 rsysdevdb.radisys.com - jkstill@dv01 SQL> @run_stats
NAME RUN1 RUN2 DIFF
-- -- --
STAT...calls to get snapshot scn: kcmgss 4002 4001 -1
STAT...deferred (CURRENT) block cleanout 3 2 -1
applications
STAT...enqueue requests 1 0 -1
STAT...free buffer requested
Re: Are too many Foreign Keys in one table bad?
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
<[EMAIL PROTECTED]>
Subject: Re: Are too many Foreign Keys in
one table bad?
Sent by:
[EMAIL PROTECTED]
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 <[EMAIL PROTECTED]> 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
>
> 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
>
>
Re: Are too many Foreign Keys in one table bad?
I'm flattered you put me in the same category as Kirti, Dan, Jared, Connor and Jonathan. If I recall the cost of my (one) trip to Australia, it would be cheaper to import you to IOUG than it would be to get us there! I'm not sure about Jared and Connor, the rest of us will be at IOUG. And for the price of a drink of Black Adder Scotch I'm sure you could get Dan to expound on Oracle and backup and recovery and blockcentric tuning all night long. Okay, make that a bottle of BlackAdder and make sure he isn't presenting first thing the next morning :) --- Mark Richard <[EMAIL PROTECTED]> wrote: > 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 <[EMAIL PROTECTED]> > > ahoo.com>Subject: Re: Are too > many Foreign Keys in one table bad? > Sent by: > > [EMAIL PROTECTED] > > 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 <[EMAIL PROTECTED]> 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 1
RE: Are too many Foreign Keys in one table bad?
I am flattered as well I am not in the league of the heavy weights like Jonathan, Connor, Cary, Anjo, Mogens, Gaja, Dan, Jared, Tom, James etc... and certainly not presenting in the upcoming Hotsos Symposium, just attending. I have a full time day (and night) job as 'Dumb But Acceptable' at Verizon Corp. I will present a couple of Quick Tip sessions at the IOUG Live! in Orlando. I would love to visit Australia (and NZ as well), but just don't know when. Ferenc has graciously offered his guest bedroom down there :) so I think I am half way there, just need to find a sponsor for the airline ticket ;) Thanks for thinking of my name, though ;) Regards, - Kirti -Original Message- Sent: Monday, January 06, 2003 9:29 PM To: Multiple recipients of list ORACLE-L I'm flattered you put me in the same category as Kirti, Dan, Jared, Connor and Jonathan. If I recall the cost of my (one) trip to Australia, it would be cheaper to import you to IOUG than it would be to get us there! I'm not sure about Jared and Connor, the rest of us will be at IOUG. And for the price of a drink of Black Adder Scotch I'm sure you could get Dan to expound on Oracle and backup and recovery and blockcentric tuning all night long. Okay, make that a bottle of BlackAdder and make sure he isn't presenting first thing the next morning :) --- Mark Richard <[EMAIL PROTECTED]> wrote: > 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 <[EMAIL PROTECTED]> > > ahoo.com>Subject: Re: Are too > many Foreign Keys in one table bad? > Sent by: > > [EMAIL PROTECTED] > > 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 <[EMAIL PROTECTED]> wrote: > > Greg, > > > > I don't think Oracle will have a real problem with 15 tables or > 1,000 > >
Re: Are too many Foreign Keys in one table bad?
And apart from the differences in cost on the simple test, you also remove the information about uniqueness and non-nullability if you don't declare the primary key, and this has an impact on the optimizer's decision tree. Bear in mind, also, that Oracle will rarely do a tablescan on the inner table of a nested loop - so you may get a fifteen table hash join if you don't have any indexes, and this MIGHT go to one of the two possible extremes of demanding nearly 14 x hash_area_size in memory, or 14 allocations of temporary extents on your temporary tablespace. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 07 January 2003 02:45 > >There can be quite a difference between using >an index on a small table, and not using one. > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
RE: Are too many Foreign Keys in one table bad?
A couple of other thoughts depending on the size of the table with the large number of foreign keys (I may have missed the exact row counts), you might want to consider bitmaps on the foreign keys in the main table depending on the uniqueness of the data. Also, if the foreign key tables are relatively small another possibility to consider would be an indexed table if the joins would naturally grab the whole table. Regards, Bill Burke "The Kinder and Gentler DBA" Live 2003 Expert Presentation - Where there's smoke there's fire - Firefighter or Arsonist IOUG University Master Class Faculty 2001-2002 "iDBA Management, High Performance Infrastructure and HA" IOUG Board of Directors 2000-2002 ODTUG Board of Directors 1996-2000 www.OracleGuru.com - All UMC and Conference Presentations are here www.KBMotorsports.biz -Original Message- Sent: Tuesday, January 07, 2003 2:59 AM To: Multiple recipients of list ORACLE-L And apart from the differences in cost on the simple test, you also remove the information about uniqueness and non-nullability if you don't declare the primary key, and this has an impact on the optimizer's decision tree. Bear in mind, also, that Oracle will rarely do a tablescan on the inner table of a nested loop - so you may get a fifteen table hash join if you don't have any indexes, and this MIGHT go to one of the two possible extremes of demanding nearly 14 x hash_area_size in memory, or 14 allocations of temporary extents on your temporary tablespace. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 07 January 2003 02:45 > >There can be quite a difference between using >an index on a small table, and not using one. > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Burke, William F (Bill) 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).
RE: Are too many Foreign Keys in one table bad?
Thanks to all for the advice.. Ps I am an Oz type living in Canada - just flew here at xmas and yes it aint cheap but ooh airline food is great! CIAO -Original Message- Sent: Monday, January 06, 2003 10:29 PM To: Multiple recipients of list ORACLE-L I'm flattered you put me in the same category as Kirti, Dan, Jared, Connor and Jonathan. If I recall the cost of my (one) trip to Australia, it would be cheaper to import you to IOUG than it would be to get us there! I'm not sure about Jared and Connor, the rest of us will be at IOUG. And for the price of a drink of Black Adder Scotch I'm sure you could get Dan to expound on Oracle and backup and recovery and blockcentric tuning all night long. Okay, make that a bottle of BlackAdder and make sure he isn't presenting first thing the next morning :) --- Mark Richard <[EMAIL PROTECTED]> wrote: > 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 <[EMAIL PROTECTED]> > > ahoo.com>Subject: Re: Are too > many Foreign Keys in one table bad? > Sent by: > > [EMAIL PROTECTED] > > 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 <[EMAIL PROTECTED]> 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) > > > > Y
RE: Are too many Foreign Keys in one table bad?
Black Adder is an excellent method to get me to shut up and leave! -Original Message- Sent: Monday, January 06, 2003 8:29 PM To: Multiple recipients of list ORACLE-L I'm flattered you put me in the same category as Kirti, Dan, Jared, Connor and Jonathan. If I recall the cost of my (one) trip to Australia, it would be cheaper to import you to IOUG than it would be to get us there! I'm not sure about Jared and Connor, the rest of us will be at IOUG. And for the price of a drink of Black Adder Scotch I'm sure you could get Dan to expound on Oracle and backup and recovery and blockcentric tuning all night long. Okay, make that a bottle of BlackAdder and make sure he isn't presenting first thing the next morning :) --- Mark Richard <[EMAIL PROTECTED]> wrote: > 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 <[EMAIL PROTECTED]> > > ahoo.com>Subject: Re: Are too > many Foreign Keys in one table bad? > Sent by: > > [EMAIL PROTECTED] > > 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 <[EMAIL PROTECTED]> 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 &
RE: Are too many Foreign Keys in one table bad?
Gregory, There are several things to consider since you are still at the design phase. This table sounds like a great candidate for denormalization. Is this an OLTP or OLAP system? How static are the values in the reftables? If the reftables are static and contain very few values, consider putting the values into the support_data table. If the data values (not counting the relationship codes) are small, the storage may be about the same if they are stored inside or outside of the database. In addition to the join performance issue, you will have to worry about insert/update/delete. To prevent locking problems, you will need to put an index on the FK columns. You now have 15 extra changes to make when you change a row. The best method to determine the optimal solution is to create test cases and measure the performance of various configurations. It will require a little bit of time right now, but may save a great deal of time in fixes/outages/redeployments when the system goes live and performance goes down. Dan Fink -Original Message- Sent: Monday, January 06, 2003 1:04 PM To: Multiple recipients of list 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) _code (FK) support_data_desc _id (PK) _code (Unique Constraint) _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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fink, Dan 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).
RE: Are too many Foreign Keys in one table bad?
okay fine, Talisker then :) --- "Fink, Dan" <[EMAIL PROTECTED]> wrote: > Black Adder is an excellent method to get me to shut up and leave! > > -Original Message- > Sent: Monday, January 06, 2003 8:29 PM > To: Multiple recipients of list ORACLE-L > > > I'm flattered you put me in the same category as Kirti, Dan, Jared, > Connor and Jonathan. > > If I recall the cost of my (one) trip to Australia, it would be > cheaper > to import you to IOUG than it would be to get us there! I'm not sure > about Jared and Connor, the rest of us will be at IOUG. And for the > price of a drink of Black Adder Scotch I'm sure you could get Dan to > expound on Oracle and backup and recovery and blockcentric tuning all > night long. Okay, make that a bottle of BlackAdder and make sure he > isn't presenting first thing the next morning :) > > > --- Mark Richard <[EMAIL PROTECTED]> wrote: > > 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 <[EMAIL PROTECTED]> > > > > > > ahoo.com>Subject: Re: Are too > > many Foreign Keys in one table bad? > > Sent by: > > > > > [EMAIL PROTECTED] > > > > > 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 <[EMAIL PROTECTED]> wrote: > > > Greg, > > > > > > I don't think Oracle
Re: Are too many Foreign Keys in one table bad?
Thanks, but I don't think I'm in the same class as
some of those names. I just keep my head down
and keep trying. :)
Jared
"Mark Richard" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/06/2003 06:23 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: Re: Are too many Foreign Keys in one table bad?
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 <[EMAIL PROTECTED]>
Subject: Re: Are too many
Foreign Keys in one table bad?
Sent by:
[EMAIL PROTECTED]
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 <[EMAIL PROTECTED]> 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
>
> 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.
>
>
