Re: When 10053 trace gives same cost for 2 join ops which join is chosen ?

Typically, it will be the first one evaluated -

A 'new option' only replaces a 'previous best' if it is BETTER than the previous.

However, the 10053 usually prints to the ceiling() integer value, and the choice may be made on the real value, so you could get:

    NL 4.8 prints as 5
    HA 4.7 prints as 5
HA chosen

or

    NL 4.5 prints as 5
    HA 4.7 prints as 5
NL chosen

Treatment of rounding is version dependent, although calculations involving bitmap indexes seem to be make full use of non-rounded values even in some of the earlier versions of Oracle.

-- 
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
Optimising Oracle Seminar - schedule updated July 27th




"Spendius" <spendius_at_muchomail.com> wrote in message
news:aba30b75.0408160812.78c14c22_at_posting.google.com...


> Hi,

> When the optimizer ends up with the same cost for 2 different

> join operations in a query, which one does it choose ?

>

> In one of these trace files I noticed that for a given query

> the calculation gave CST=5 for the NL and HA paragraphs (and

> 6 for the sort-merge), but Oracle would keep using the NL join

> and it resulted *every time this query was executed* in an execution

> time of more than 1 second => I hinted the SELECT with USE_HASH

> (as I could see the cost was the same as NL) and from now on this

> query's been performing very well (duration of a few 100ths of second,

> never more). In the EXPLAIN PLAN I could check this change from NL to

> HA was the only one occuring. So keeping the NL join op against the

> HA one was a bad choice.

>

> Thanks.

> Spendius