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