| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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 || ''''
|| ')';
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
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'
);
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_at_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_at_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 -1applications
STAT...enqueue requests 1 0 -1 STAT...free buffer requested 0 1 1 LATCH.active checkpoint queue latch 2 0 -2 STAT...messages sent 0 2 2 LATCH.undo global data 2 5 3 LATCH.enqueue hash chains 0 4 4 LATCH.redo allocation 9 13 4 LATCH.session allocation 0 8 8 LATCH.enqueues 0 12 12 LATCH.session idle bit 0 14 14 STAT...recursive cpu usage 54 40 -14 LATCH.cache buffers lru chain 20 1 -19 LATCH.shared pool 4 28 24 LATCH.checkpoint queue latch 44 1 -43 STAT...redo size 20944 21016 72 LATCH.library cache 8084 8194 110 STAT...session uga memory 3184 0 -3184 STAT...buffer is not pinned count 4000 8000 4000 STAT...table scan blocks gotten 4000 0 -4000 STAT...table scans (short tables) 4000 0 -4000 STAT...table fetch by rowid 0 4000 4000 STAT...consistent gets 4005 8006 4001 STAT...session logical reads 20017 8020 -11997 STAT...db block gets 16012 14 -15998 LATCH.cache buffers chains 40098 16081 -24017 STAT...table scan rows gotten 400000 0 -400000
28 rows selected.
The RUN2 column contains the stats for the SQL using an index. There are substantial resource savings here.
This was on a table with 100 rows, it all fits in one 8k block, yet the use of an index made the code much more efficient.
My thanks to Cary Millsap, and everyone else on this list that has pointed this out.
Jared
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Jared.Still_at_radisys.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).Received on Mon Jan 06 2003 - 20:03:51 CST
 |
 |