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.*********************************************************************2
