"Merge join" and "NO_MERGE" hint

[Santana]

"Merge join" and "NO_MERGE" hint

Hi all.
I’m very confused and I nee you to clarify me!

I now the idea of a merge join:

A merge join basically sorts all relevant rows in the first table by
the join key , and also sorts the relevant rows in the second table by
the join key, and then merges these sorted rows.

I understand what this mean, however I don’t understand the hint
“NO_MERGE”, I don’t if the idea of this hint is related to the merge
join concept. I don’t understand the description that is in the ORACLE
website.

For example :

SELECT *
FROM CUSTOMER C
WHERE CUST.TYPE IN (‘O’,’R’)
AND CUST.STATUS=’A’
AND CUST.VERSION ( SELECT MAX(VERSION)
FROM CUSTOMER C1
WHERE C1.CUSTOMER_ID = CUST.CUSTOMER
AND C1.TYPE = CUST.TYPE
)


This query takes long time the retrieve the result, something as 3min,
but with this simple change the result is retrieved in a few seconds:
10-12 seconds.




SELECT *
FROM CUSTOMER C
WHERE CUST.TYPE IN (‘O’,’R’)
AND CUST.STATUS=’A’
AND CUST.VERSION ( SELECT /*NO_MERGE*/ MAX(VERSION)
FROM CUSTOMER C1
WHERE C1.CUSTOMER_ID = CUST.CUSTOMER
AND C1.TYPE = CUST.TYPE
)


What do this hint ? Avoid the order of “VERSION” of the “CUST” ? This
hint applicable to “CUST” or “C1”( inline view) table ?

I don’t understande how this hint works IN THIS SITUATION.



Thanks.
Santana

[Gopakumar Pandarikkal]

AFAIK. No_merge hint means "don't use merge algorithm for joining the tables given in the from clause."  Then it will try nested loop or hash algorithm only. If the tables are of almost equal size merge join is very effective. But in this case as there is only one table there wont be any use for that hint.

regards
Gopa

--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle...@googlegroups.com
To unsubscribe from this group, send email to
Oracle-PLSQL...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en

[ddf]

Comments embedded.

On Feb 3, 9:56 pm, Gopakumar Pandarikkal <pandarik...@gmail.com>
wrote:

> AFAIK. No_merge hint means "don't use merge algorithm for joining the tables
> given in the from clause."  Then it will try nested loop or hash algorithm
> only. If the tables are of almost equal size merge join is very effective.
> But in this case as there is only one table there wont be any use for that
> hint.
>
> regards
> Gopa
>

The NO_MERGE hint (and I expect the OP wrote it correctly in his
running
code as both query examples posted would produce syntax errors due to
the missing IN operator and the hint is missing the necessary + sign)
instructs Oracle to not follow the expected view merge operation by
instantiating the in-iine view before running the rest of the query.

The NO_MERGE hint has nothing to do with the NERGE JOIN operation, it
prevents Oracle from rewriting the inner and outer queries into a
new,
single query which may be less efficient than the non-merged
statements. Read here to understand what view merging is and how it
can affect performance:

http://blogs.oracle.com/optimizer/2010/10/optimizer_transformations_view_merging_part_1.html