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