Plan hash value: 3937517195
0 recursive calls
0 db block gets
242 consistent gets
0 physical reads
In such a situation I would have first proceed as follows
I recently migrated Oracle 220.127.116.11 to Oracle 11g but the querys doesn't work as I hope
and would see if the performance is back to its acceptable response time
11g> alter session set optimizer_features_enable=’18.104.22.168'; 11g> run the query again
But the hash join occurs only when the join condition is an equality. May be you are referring to a non-equality occuring in predicates that do not participate in the join condition
In addition to that, I suspect that this may cause additional problems when doing the join -- a couple of months ago I saw a similar case with a slow in-memory hash join, where the problem was because of non-equality predicate.
Mohamed Houri wrote:
But the hash join occurs only when the join condition is an equality. May be you are referring to a non-equality occuring in predicates that do not participate in the join conditionthe join predicate contained both equality and non-equality parts. The equality predicate had very weak seletivity and served as an access predicate, the inequality predicate had very strong selectivity and served as a filter predicate. As a result, the hash join was similar to a cartesian join -- i.e. first it joined every row to almost every row in the second table, and then applied the filter predicate the the resulting huge data set.