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
On Feb 4, 2011 4:43 AM, "Santana" <paulito.sant...@gmail.com> wrote:
"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 Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
--
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-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
