RE: full-scan vs index for "small" tables
- From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
- To: <Laimutis.Nedzinskas@xxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 28 Jun 2006 08:44:21 -0500
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Laimutis Nedzinskas
Sent: Wednesday, June 28, 2006 4:47 AM
To: oracle-l@xxxxxxxxxxxxx
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>
Other related posts:
