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

For a given SQL statement and optimizer goal, the lowest cost is supposed to be the fastest query path. So the answer to your question ought to be YES. Inevitably, the calculations used by Oracle in estimating the cost do not always come to the right answer, so the answer to your question has to be qualified with 'so long as nothing goes wrong, and Oracle does not get deceived by the stats it finds and ...'. This is why you can hint an access path, which is given a higher cost, but executes more quickly.

In your case, though, I think the specific example you gave was comparing a plan using ALL_ROWS (or CHOOSE when statistics were in place) with a plan using FIRST_ROWS.

When using FIRST_ROWS, the total cost of execution is not the critical factor - the cost (time) of getting the first row from the result set is the critical factor. Consequently Oracle may find a FIRST_ROWS path __which it wouldn't otherwise discover__ that is nominally very expensive in terms of getting the whole result set, but cheap in terms of getting the first row. Combining this deliberate feature of the optimiser with my comments above on errors, you can appreciate that Oracle can easily find a 'more costly' path that contrarily executes faster when you use the FIRST_ROWS hint.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

Paul Moore wrote in message ...


>

>If I have two execution plans, with differing costs as calculated by the

>CBO (at the same time, so the data or stats haven't changed), is it

>reasonable to assume that the plan with the lowest cost is the one which

>should run in the shortest execution time?

>