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

One point of clarification: How fast a query execution plan runs has nothing to do with which optimizer you're using. In other words, it's not that the concept "never index a small table" was right during the RBO years and became wrong during the CBO years. It was wrong during the RBO years as well.  

RBO is dramatically inferior to CBO in every case except for the one where the operational manager doesn't do a good job of making sure that the statistics are a reasonable representation of the production data.    

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 Laimutis Nedzinskas Sent: Wednesday, June 28, 2006 4:47 AM
To: oracle-l_at_freelists.org
Subject: RE: full-scan vs index for "small" tables  

>On Behalf Of Nigel Thomas

>To be fair, the sentence continues (my bold, from 10gR1):

>"...a full table scan might be cheaper than an index range scan"
>or by implication it might not. Or they could be the same. Or England
could win the World Cup. Or whatever. Looks like they've got all the angles covered there then.  

And surely I am lost. What are the best practices for SQL programmer in the modern post_Rule_Based_Optimizer Oracle then?  

• Rely on unreliable CBO? But all we have already heard stories of even Oracle fighting it's own CBO for *stable* data dictionary performance. And CBO is inherently unstable at least in the beginning of application's life cycle.
• Onlines? But they may or may not to cover all SQL. And outlines will not cover literal SQL("legal" literal SQL), will they?
• Hint every single statement? That one seems to be the option, doesn't it?

P.S. Just wandering: is there a hint to *disable* statistics at statement or session level? I mean, let CBO use *defaults* making it kind of rule based actually.      

Thank you in advance,

Laimis N.    

Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer
<http://www.landsbanki.is/disclaimer>

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