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 > 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 > athttp://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
