> 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 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: [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).
