"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
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
