Re: CBO calculates lower cost, but runs slower...

Paul Moore doodled thusly:

>
>Of course, if the FIRST_ROWS goal causes the cost calculations to be
>done completely differently, I'm going to feel very stupid :-)
>

I don't think so. Mind you, in 8 and 8i there are so many point release variations that it's almost impossible to say anything for sure. But I'd almost bet that changing from default to FIRST_ROWS is not going to change the calculations. It will weigh things differently and select plans it wouldn't otherwise, but the basic cost calculations for each of those plans would be the same.

As Jonathan pointed out, one of the major reasons for "clunks" in the optimizer is the lack of proper weighting of physical reads vs logical reads. In some of the later point versions, there is an attempt at doing this. Factor of 500, wasn't it Jonathan? IIRC, we had a similar chat a while ago and that number springs back to mind?

With the original weighting, all you need is for an index to be in memory and bang, the "costlier" path will be faster. But the optimizer will only pick that one if set to FIRST_ROWS or hinted.

As I said to Connor, if you set default to FIRST_ROWS and eventually need to use the export/import utilities, watch out!

Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam Received on Tue Nov 20 2001 - 10:01:34 CST