If you can change it to an IOT, it may be beneficial. There's no blanket clause to be used that says 'Always do this'.
I higly encourage folks on this list to setup and use the run_stats method of comparing different access methods. This is something Tom Kyte put together. It is very simple to use. URL: http://osi.oracle.com/~tkyte/runstats.html I've attached my versions of the scripts for your convenience. You can use these to easily compare unindexed vs indexed reads on small tables, indexed vs IOT, etc. Jared "Jamadagni, Rajendra" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 04/03/2003 10:05 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: RE: oracle full table scan Thanks Jared, What if my developer is selecting all or most of the records from the table and not all the columns in the select list are in the index that should have been used? I understand your point, in fact to use Jonathan's words .. "should a small lookup table BE an index (IOT)?" ... I am testing this approach here and have found some performance benefit out of it. Cheers Raj -------------------------------------------------------------------------------- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -----Original Message----- Sent: Thursday, April 03, 2003 1:01 PM To: [EMAIL PROTECTED] Cc: Jamadagni, Rajendra Importance: High Raj, Indexing small tables is a good thing if you are doing single row lookups. An index read and lookup by rowid is much more scalable than doing an FTS, even if the table is only 2 blocks. Jared
view.sql
Description: Binary data
The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification.
grants.sql
Description: Binary data
The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification.
run_stats.sql
Description: Binary data
The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification.
Runstats.sql This is the test harness I use to try out different ideas. It
shows two vital sets of statistics for me The elapsed time difference between
two approaches. It very simply shows me which approach is faster by the wall
clock How many resources each approach takes. This can be more meaningful then
even the wall clock timings. For example, if one approach is faster then the
other but it takes thousands of latches (locks), I might avoid it simply
because it will not scale as well. The way this test harness works is by saving
the system statistics and latch information into a temporary table. We then run
a test and take another snapshot. We run the second test and take yet another
snapshot. Now we can show the amount of resources used by approach 1 and
approach 2.
Requirements
In order to run this test harness you must at a minimum have: Access to
V$STATNAME, V$MYSTAT, and V$LATCH If you want to use the view as I have, you
must be granted select DIRECTLY on SYS.V_$STATNAME, SYS.V_$MYSTAT, and
SYS.V_$LATCH. It will not work to have select on these via a ROLE. You can
still run the test harness, you just will not be using the view "STATS" I have
below (substitute in the query text in the PLSQL block where I reference the
view STATS). The ability to create a table -- run_stats -- to hold the before,
during and after information. You should note also that the LATCH information
is collected on a SYSTEM WIDE basis. If you run this on a multi-user system,
the latch information may be technically "incorrect" as you will count the
latching information for other sessions - not just your session. This test
harness works best in a simple, controlled test environment.
The table we need is very simple:
create table run_stats
( runid varchar2(15),
name varchar2(80),
value int
);
and if you can get direct grants on the V$ tables necessary (or have your DBA
create this view and grant SELECT on it to you), you can create this view:
create or replace view stats
as select 'STAT...' || a.name name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
union all
select 'LATCH.' || name, gets
from v$latch;
Now the test harness itself is very simple. Here it is:
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
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
end loop;
dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
insert into run_stats select 'after 2', stats.* from stats;
end;
/
and thats it, now after that block runs, you'll see the wall clock difference
between the two approaches. You can see the really important stuff using this
query:
select a.name, b.value-a.value run1, c.value-b.value run2,
( (c.value-b.value)-(b.value-a.value)) diff
from run_stats a, run_stats b, run_stats c
where a.name = b.name
and b.name = c.name
and a.runid = 'before'
and b.runid = 'after 1'
and c.runid = 'after 2'
and (c.value-a.value) > 0
and (c.value-b.value) <> (b.value-a.value)
order by abs( (c.value-b.value)-(b.value-a.value))
/
=
table.sql
Description: Binary data
The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification.
test_harness.sql
Description: Binary data
The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification.
defaults.sql
Description: Binary data
