RE: full-scan vs index for "small" tables

Exactly. That's a place where CBO should have no trouble whatsoever making the right decision (index access).    

Cary Millsap

Hotsos Enterprises, Ltd.

http://www.hotsos.com

Nullius in verba  

Hotsos Symposium 2007 / March 4-8 / Dallas

Visit www.hotsos.com for curriculum and schedule details...

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Wolfgang Breitling Sent: Thursday, June 29, 2006 6:44 AM
To: Laimutis.Nedzinskas_at_landsbanki.is
Cc: oracle-l_at_freelists.org
Subject: RE: full-scan vs index for "small" tables  

At 03:20 AM 6/29/2006, Laimutis Nedzinskas wrote:  

I can illustrate my point using one example (which seems to be one the the biggest CBO issues actually, discussed many times before):  

I have a "select where type='OPENED' "  

No need to say that type='OPENED' is 0.1% of all records, the rest are 'CLOSED'.   I built a histogram. Value 'OPENED' happened not to be there because... the were no records with type='OPENED' at the time. Plan was full scan of course.

That is not my experience at all. If the value in the predicate is NOT in the (frequency) histogram then in my experience hat is synonymous for "very rare" for the CBO and it uses the index. From all you are saying I get the impression that you are using a different CBO from the one I am using.

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l