Oracle-L: RE: oracle full table scan

From: <Jared.Still_at_radisys.com>
Date: Thu, 03 Apr 2003 09:58:46 -0800
Message-ID: <F001.00579931.20030403095846@fatcity.com>

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" <Rajendra.Jamadagni_at_espn.com> Sent by: root_at_fatcity.com
04/03/2003 05:28 AM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        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" <ORACLE-L_at_fatcity.com> 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
> --

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Jared.Still_at_radisys.com

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: ListGuru_at_fatcity.com (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).



text/plain attachment: ESPN_Disclaimer.txt

Received on Thu Apr 03 2003 - 11:58:46 CST