Re: Find a better plan than that computed by the optimizer ??

There are various reasons that you can find better plans than the optimizer.

First of all, the optimizer is programmed to stop checking after a (variable) time limit, so it may never reach the best plan.

Secondly, the optimizer has an algorithm to generate a set of join orders for testing, and may therefore skip over an order that would be better than any order it will otherwise generate. (This is, in part, a side-effect of the time-limit above).

Third, the optimizer has a number of assumptions built in that may not be true for your system, so the best cost path it finds may be totally inappropriate for the system you are running.

Finally, you know the business better than the optimizer, so there may be some information available to you that allows you to select a path that is technically unsuitable as far as the optimizer is concerned.

-- 
Regards

Jonathan Lewis

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

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated Jan 21st 2005






"Spendius" <spendius_at_muchomail.com> wrote in message 
news:aba30b75.0502220647.4ac37e07_at_posting.google.com...


> Hi,

> Did any of you *ever* manage to find a better plan

> than the optimizer by appropriately hinting your

> query ?

> The only time I succeeded in finding a lower cost

> than it, it was (thanks to a 10053 trace) because

> it reached the same cost for a NL and a HASH but

> kept the nested loop in its final solution => I hinted

> with the USE_HASH and it appeared that for the same

> cost calculation the hash join was much quicker.

>

> But can you be, sometimes, better than the optimizer,

> could it be that it missed some join combinations or

> whatever ?

>

> Thanks a lot.

> Spendius