Re: EXPLAIN PLAN madness!

Hi,

Think volume of the 2 environments are in cause or data key distribution in the tables.
If DB1 table have 40% of same key value table scan may be performed or hash join
If DB2 Get only 10% of same key values nested loop can be performed

in fact, if an index on 2 keys is not selective on the first key, optimizer can choose Full scan or hash join until the table grow or the selectivity is better.

Try with a hint select /*+FIRST_ROWS*/ .... in the query

Or

Try this instance parameters:

HASH_JOIN_ENABLED=FALSE; -- to avoid hash join exept if you get hash index, else hash join plan must construct hash join table before perform and can take several hours before go.... OPTIMIZER_INDEX_CACHING=100;
OPTIMIZER_INDEX_COST_ADJ=10; --to favour the nested loop plan, this say scan an index is only 10%of the cost to scan a table

You can try this parameters with an alter session. Received on Thu Nov 15 2001 - 08:12:24 CST