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

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 Received on Mon Aug 16 2004 - 11:12:06 CDT