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-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Thursday, April 03, 2003 1:01 PM
To: [EMAIL PROTECTED]
Cc: Jamadagni, Rajendra
Subject: RE: oracle full table scan
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
"Jamadagni, Rajendra" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
04/03/2003 05:28 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: RE: oracle full table scan
To answer the original question ...
1. use following query to see which tables are part of FTS ... it is a
point in time information. (Query from www.ixora.com I think).
SELECT usr.name oowner, ob.name oname
FROM ( SELECT obj
FROM sys.X_$BH
WHERE TO_NUMBER(bitand(flag, POWER(2,19))) > 0
GROUP BY obj) bh,
sys.obj$ ob,
sys.USER$ usr
WHERE ob.dataobj# = bh.obj
AND ob.owner# = usr.USER#
ORDER BY usr.name, ob.name
/
2. FTS can happen for many reasons ... if Oracle is performing FTS on a
small table, that's the way to do it. Remember when you create an index
Oracle had to perform 2 IOs, one for INDEX lookup and (if required) one
for Table lookup. Sometimes associated costs dictate that a FTS is cheaper
than the combined cost (of index lookup and table lookup), so Oracle
prefers that.
One upon a time, I used to think on the same lines, but the bright minds
on this list have time and again proven that FTS, isn't a bad thing after
all. Sometimes it is, but not ALL the times.
Creating indexes is not the solution, a careful analysis of the logic
implemented in the SQL is also required, and you will be surprised that,
just by making the query changes, the performance gain can be achieved.
PS: Stephane, you probably have this on the top of your "Oracle Myth" list
... right?
YMMV
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 -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, April 03, 2003 2:58 PM
> Dear All,
>
> is there any way to find which tables (table name) are
suffering from
> full table scan ,so that i can create indexes on them to enhance the
> performance.
>
>
> Thanks
>
> Arvind
> --
*********************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************1
