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

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

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

Other related posts: