Oracle-L: RE: oracle full table scan

From: <Jared.Still_at_radisys.com>
Date: Thu, 03 Apr 2003 11:34:07 -0800
Message-ID: <F001.00579B6A.20030403113407@fatcity.com>

If you can change it to an IOT, it may be beneficial.

There's no blanket clause to be used that says 'Always do this'.

I higly encourage folks on this list to setup and use the run_stats method of comparing different access methods. This is something Tom Kyte put together. It is very simple to use.

URL: http://osi.oracle.com/~tkyte/runstats.html

I've attached my versions of the scripts for your convenience.

You can use these to easily compare unindexed vs indexed reads on small tables, indexed vs IOT, etc.

Jared

"Jamadagni, Rajendra" <Rajendra.Jamadagni_at_espn.com> Sent by: root_at_fatcity.com
04/03/2003 10:05 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

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

The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail.

This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact Postmaster_at_fatcity.com for clarification.

Content-Type: text/plain; name="ReadMe.txt"; charset="us-ascii" Content-Transfer-Encoding: 7bit

--=_mixed 006BA47E88256CFD_=--

-- 
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).



application/octet-stream attachment: view.sql



application/octet-stream attachment: grants.sql



application/octet-stream attachment: run_stats.sql



text/plain attachment: run_stats.txt



application/octet-stream attachment: test_harness.sql



application/octet-stream attachment: defaults.sql

Received on Thu Apr 03 2003 - 13:34:07 CST