NO_MERGE causes merge

8.1.6

Just want to make sure I understand this.

This query uses a Merge operation:

SELECT /*+ RULE */ * FROM Dual d1, Dual d2 WHERE d1.Dummy = d2.Dummy;

So, a perfect candidate for the NO_MERGE hint:

SELECT /*+ RULE NO_MERGE */ * FROM Dual d1, Dual d2 WHERE d1.Dummy = d2.Dummy;

It works well, as EXPLAIN PLAN shows a HASH JOIN being used instead.

Same with these two:

SELECT /*+ RULE NO_MERGE(d1) */ * FROM Dual d1, Dual d2 WHERE d1.Dummy = d2.Dummy;

SELECT /*+ RULE NO_MERGE(d2) */ * FROM Dual d1, Dual d2 WHERE d1.Dummy = d2.Dummy

Now, lets choose one that does not use a merge;

SELECT /*+ RULE */ * FROM Dual d1, Dual d2;

The optimizer chose nested loops.

So, let's give a useless hint:

SELECT /*+ RULE NO_MERGE*/ * FROM Dual d1, Dual d2;
SELECT /*+ RULE NO_MERGE(d1) */ * FROM Dual d1, Dual d2;
SELECT /*+ RULE NO_MERGE(d2) */ * FROM Dual d1, Dual d2;

Now it does a MERGE JOIN CARTESIAN. Isn't that what I didn't want?

So, I am guessing that NO_MERGE invoke the cost based optimizer, which hit RULE headon and both were ignored. Thus, causing the equivalent of:

SELECT /*+ ALL_ROWS */ * FROM Dual d1, Dual d2;

which uses the same plan.

OK, so now I dutifully try:

SELECT /*+ ALL_ROWS NO_MERGE*/ * FROM Dual d1, Dual d2;
SELECT /*+ ALL_ROWS NO_MERGE(d1)*/ * FROM Dual d1, Dual d2;
SELECT /*+ ALL_ROWS NO_MERGE(d2)*/ * FROM Dual d1, Dual d2;

and yet it still does a MERGE JOIN CARTESIAN. Why is that?

Further,

I tried

SELECT /* ALL_ROWS */ * FROM Dual d1, (SELECT /*+ ALL_ROWS */ * FROM Dual) d2 WHERE d1.Dummy = d2.Dummy;

EXPLAIN PLAN showed a HASH JOIN of two TABLE ACCESS FULLs on DUAL.

However, applying an apparently useless hint:

SELECT /* ALL_ROWS */ * FROM Dual d1, (SELECT /*+ ALL_ROWS NO_MERGE*/ * FROM Dual) d2 WHERE d1.Dummy = d2.Dummy;

causes the inner table to throw its TABLE SCAN FULL into a view before the HASH JOIN is done. The hint without the argument is actually valid in the inner query block, but why should it matter, I wasn't doing a merge?

Brian Received on Tue Dec 25 2001 - 10:50:07 CST