Oracle-L: RE: oracle full table scan

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Thu, 03 Apr 2003 10:05:10 -0800
Message-ID: <F001.0057994C.20030403100510@fatcity.com>

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: ORACLE-L_at_fatcity.com
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

"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: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.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 - 12:05:10 CST