why nested_loop (9i) and not Hash join (8i) ?

[khussain]

We have a query that runs for 4 mins in 8i and hours! in 9i:

Looking at the explain plans, I see that there is a very expensive nested loop in 9i, where as it does a hash join in 8i.
Indexes are the same on both, but not used.

Then I noticed that in our 9i the hash_join_enabled param was set to false (it is true in 8i)...so I altered the session to true.
But it still does a nested loop.

Any ideas ? What else can I look at ? Can I force hash join some how ?

[sreddy]

Look at /* USE_HASH */ hint to force hash join. Whats your init.ora compatible parm set to 81x or 90x ??

May be its using 81x optimizer even in 9i ?

[khussain]

compatible=9.2.0

What else would make the CBO select NESTED LOOPS instead of HASH JOIN ?

[slimdave]

This could be affected by changes in init parameters, particularly those that affect the optimizers "opinion" on how fast it is to access indexes in particular ways.

I'm thinking of optimizer_index_cost_adj, hash_area_size, sort_area_size for example, so you might look at what changes have occurred in your init parameters.