Re: full-scan vs index for "small" tables
- From: Mladen Gogala <gogala@xxxxxxxxxxxxx>
- To: breitliw@xxxxxxxxxxxxx
- Date: Thu, 29 Jun 2006 22:21:13 -0400
On 06/29/2006 02:11:38 PM, Wolfgang Breitling wrote:
> I have to disagree that the CBO is undeterministic. Given the same inputs
> (i.e.
> statistics and parameters) it comes up with the same output, i.e. access path.
It's not indeterministic, but is extremely complex and its very hard to predict
the output. What I really need is a "use an index if it exists" parameter that
Oracle doesn't want to give us. Sometimes, I don't want optimizer to be smart,
I want an index to be used if it exists, regardless of statistics, period. That
can be achieved by OPTIMIZER_INDEX_* parameters, but even that is not
bulletproof.
RBO has had many advantages, that's why it's still so appealing. No matter how
hard Oracle is trying, they don't seem to be able to make it go away. If you are
dealing with legacy applications, you want RBO, plain and simple. No outlines,
no uncertainties, just simple and fully deterministic RBO. You don't have
manpower
to re-write or tune old applications written using Oracle*Forms, CICS or
something
like that. My advice in such cases was usually to delay upgrade to the last
possible
moment. Such advice was usually followed. For a pure OLTP instance, all I want
is
that an application will use index, if available. I don't want to know about
hash
join, clustering factor or alike: I want index used, period. When I want to
have a
setup like that, I use the OPTIMIZER_INDEX_* and FIRST_ROWS on the session
level, to
make sure that the behavior is right. Few SQL statements that still do not
behave are
then fixed by hints.
Wolfgang, RBO is so appealing for a reason. Resistance to CBO is so great for a
reason.
I've been in a situation that I needed RBO on version 9 and I've set optimizer
mode
for a session to rule. That is the simple solution, practiced pretty much
everywhere
you go. In an OLTP environment, adaptability is just a fad. You want your
transaction
to work the same way, all the time. No adapting, evolving or adjusting.
--
Mladen Gogala
http://www.mgogala.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
