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

>[Cary Millsap]
>The best plan for a query on a big table that is loaded in a disorderly
fashion may actually be different than the best plan for the same big table loaded a different way. RBO is too dumb to notice. CBO notices, but then we yell at it for being smart.  

Randomness in the software is the last I would need as a software engineer.
My experience is such that CBO may produce 300% better plan but it may produce 1000% or even more worse plan too.  

Some clever guy will come and explain from the 10053 trace how CBO perfectly worked.
CBO relevant statistics and parameters will be blamed of course. Shifting responsibilities is a smart thing to do but I do not buy it entirely.
I do not buy the blame "collect accurate statistics". Why aren't you so smart to do it automatically? It can not be done? Then we are in AI territory.
Kind of neural network learning. Such thing has it's applications but not for OLTP. Not yet.  

I can illustrate my point using one example (which seems to be one the the biggest CBO issues actually, discussed many times before):  

I have a "select where type='OPENED' "  

No need to say that type='OPENED' is 0.1% of all records, the rest are 'CLOSED'.   I built a histogram. Value 'OPENED' happened not to be there because... the were no records with type='OPENED' at the time. Plan was full scan of course.
Minutes after I built the histogram again: finally, 'OPENED' was there.  

No need to say that there is index on type column and it is highly effective for "select where type='OPENED'". It is not so effective on "select where type='CLOSED'". Actually, it is not so much worse - may be 3 times worse.  

Now - what are the best sql developer practices to tackle this darned problem??? There are more but let's agree on this one at least.    

Regards, Laimis N.

Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer

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