why did not "no_merge" work? [message #569589] |
Tue, 30 October 2012 01:56  |
lzfhope
Messages: 61 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?
|
|
|
|
|
|