Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Index behavior

RE: Index behavior

From: Wolfgang Breitling <>
Date: Wed, 05 Nov 2003 13:04:34 -0800
Message-ID: <>

But that doesn't really explain why the optimizer chooses an FTS with a predicate that presumable is more selective (name like 'ABC%') and an index scan with a predicate that presumable is less selective (name like 'AB%'). I could understand it if it were the other way around. Is there a histogram on the name column?

At 11:34 AM 11/5/2003, you wrote:
> Your problem is not with the index, but rather the cost based
> optimizer. Most of us have been beat severely over the head and
> shoulders through the years that full table scans are a BAD thing, me
> included BTW. Well, it's time for the old dog to learn new tricks. So
> that I'm not a long winded person, take a look in Select magazine, 3rd
> qtr 2003, for the article "In Defense of Full Table Scans" by Jeff
> Maresh. For a long time the CBO was a mystery to me as well especially
> when it did unexpected things like this. I've applied Jeff's ideas on
> computing an index's efficiency to see if it explained what the CBO
> did. Amazingly in 95% of the cases I've analyzed it made absolute sense.
> I'm including Jeff with a courtesy copy of this message so that
> 1) I can pat him for making the waters clear and 2) so he can add
> anything he desires.
>Dick Goulet
>Senior Oracle DBA
>Oracle Certified 8i DBA

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation

Please see the official ORACLE-L FAQ:
Author: Wolfgang Breitling

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (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).
Received on Wed Nov 05 2003 - 15:04:34 CST

Original text of this message