9i optimizer

[Patti Johnson]

Take a look at your settings for optimizer_index_caching and
optimizer_index_cost_adj.
You can use these to influence the 'cost' assigned to index reads. Metalink note 243269.1 has more details but basically
setting optimizer_index_cost_adj to a value lower than the default of 100 will make index read appear less expensive.
These parameters are session modifiable so you can play with then in sqlplus without bouncing the db.

Patti

[mangor]

Thanks Pat. I was thinking about that feature also. I'm just having different results on all my instances. I run the same gather stats on all instances. But some instances are using nested_loops for this particular query while other instances are using HASH_JOIN. I don't understand what could be causing this.