Home » SQL & PL/SQL » SQL & PL/SQL » why did not "no_merge" work? (ORACLE 10G,r2,LINUX )
icon5.gif  why did not "no_merge" work? [message #569589] Tue, 30 October 2012 01:56 Go to next message
lzfhope
Messages: 65
Registered: July 2006
Member
hi,
a sql with "no_merge" hint like below did not work.
 
      select /*+ no_merge(a) */  * from (
        select dept_no,dept_name,level lev ,'parent' relType from department
          start with dept_no=10 connect by dept_no=prior parent_dept_no
        union  
        select dept_no,dept_name,level lev,'child' relType  from department 
          start with dept_no=10  connect by parent_dept_no=prior dept_no
      ) a  where a.lev=1 and a.relType='parent'  
  


for example, data could be this:
  dept_no   parent_dept_no  dept_name
  10        5               d1
  5         1               d2
  20        10              d3
  1                         d_top
  I hope the sql could return collection like this:
    DEPT_NO DEPT_NAME                   LEV RELTYPE
  ---------- -------------------- ---------- -------
        10 d1                            1 parent

  but,oracle returns two rows:
    DEPT_NO DEPT_NAME                   LEV RELTYPE
  ---------- -------------------- ---------- -------
        10 d1                            1 child
        10 d1                            1 parent
  

at last,i found ,in the sql plan ,step "connect by with filtering" executed before "UNION-ALL"
,so the hint "/*+no_merge() */ did not work,the view "a" is merged!!!
why?
Re: why did not "no_merge" work? [message #569592 is a reply to message #569589] Tue, 30 October 2012 02:19 Go to previous message
Michel Cadot
Messages: 59982
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you (think you) find wrong results, then ONLY Oracle can answer you: open a SR.

Regards
Michel
Previous Topic: Query where condition has 4 rows in table but result is zero rows
Next Topic: remove spaces by excluding double quotes from a string in sql
Goto Forum:
  


Current Time: Thu Dec 18 12:30:44 CST 2014

Total time taken to generate the page: 0.05548 seconds