Re: RE: Index behavior

I went thru the document provided by Daniel and also Metalink DocID NOTE.67522.1

But my scenario is totally different from what they have explained.

I didn't expect oracle to USe index all the time but why it is using INDEX scan for 'AB%' and NOT 'ABC%'.

Thanks everyone. Any help on this regard will be highly appreciated.

Thanks
-Sami

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Wed, 05 Nov 2003 13:04:34 -0800

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:
>Sami,
>
> 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
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.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).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Saminathan
  INET: saminathans_at_myrealbox.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).