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

 Are any of the CBO assumptions (like the Predicate Independence assumption below in Mark's example) modifiable by parameters? I am merely tossing this out - I am not sure if this would wreak havoc or not. =) A curiosity.

-charles schultz

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]
Sent: Wednesday, June 28, 2006 11:28 AM
To: cary.millsap_at_hotsos.com; oracle-l_at_freelists.org Subject: RE: full-scan vs index for "small" tables

And I'd go so far as to say that a pretty significant number of Category I cases fall into the class of problems related to data in a table where columns are correlated with each other. That is, to borrow the example that Jonathan used in Zurich last week, you have "people born in December", which is going to be roughly 1/12 of the population. You have "people whose astrological sign is Taurus", again, roughly 1/12 of the population. But, what happens when you query "people born in December whose astrological sign is Taurus"? Well, since Taurus is folks whose birthdays are April 20th to May 20th, the result is zero. But, the optimizer doesn't know that, it's expecting 1/144th of the population.

Cases like the above are where the optimizer can go wrong. It simply doesn't know enough about the data and it's distribution to make the correct decision.

-Mark

--
Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

Ours is the age that is proud of machines that can think and suspicious
of men who try to.  --H. Mumford Jones, 1892-1980


-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Cary Millsap
Sent: Wednesday, June 28, 2006 12:00 PM
To: oracle-l_at_freelists.org
Subject: RE: full-scan vs index for "small" tables

> ...need stable sql plans.

But the whole point of the CBO is that execution plan stability is
inferior to execution plan adaptation to changing circumstances. As
Jonathan Lewis points out very well, all it takes is the insertion of a
single row to make the True Best Plan change from one execution to
another.

If you truly want plan stability, then you want stored outlines, do you
not?

Certainly, there are two distinct categories where CBO messes up:

 I. Where it has been misinformed by the data it uses to make decisions.
II. Where it makes poor decisions based upon truly representative data.

My experience is that most problems that people think are category II
problems are really category I problems in disguise. The difference can
be revealed by inspection of 10053 data.

I do recognize the existence of category II problems as well. It's just
that I think they're considerably rarer than most people believe.


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...

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Laimutis Nedzinskas
Sent: Wednesday, June 28, 2006 10:11 AM
To: oracle-l_at_freelists.org
Subject: RE: full-scan vs index for "small" tables


 


 > On Behalf Of Cary Millsap

 > 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.

OK. To clarify my point:

Case1: In the world where sql developer says literally "a man must not
have to think" CBO is a right thing. Let the software think. 

Case2: in the world "do once and forget" I need stable sql plans. The
"operational manager doesn't do a good job" does not work here. There is
no problem to build even simple counter cases when CBO goes astray. 
What sql developer best practices would be then? 

To the previous list I can think of to add one more:
- bundle "good" statistics into your product setup.







Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer
--
http://www.freelists.org/webpage/oracle-l


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


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


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