Hierarchical query with join (lookup)
lvbnhbqNov 24 2006 — edited Nov 28 2006Is it possible to make Oracle (9.2) execute hierarchical query on one table first and then to join? Here's my scenario:
select ename,
cursor (SELECT ename, dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
START WITH e.empno = e1.empno
CONNECT BY PRIOR e.empno = e.mgr)
from emp e1
My problem is with nested cursor query. Actually I have two 10M+ tables, one of wich is self-related, another is lookup. I need to return a subtree from one table along with some fields from lookup table.
Nested query may return up to 1000 records. The query above (its nested part) doesn't return results in affordable time. Similar query
select ename,
cursor (SELECT ename,
(select dname from dept d WHERE e.deptno = d.deptno) dname
FROM emp e
START WITH empno = e1.empno
CONNECT BY PRIOR e.empno = e.mgr)
from emp e1
takes less than 1 second, but I'm still not satisfied as I need a number of fields from lookup(s) and do not want to execute a subquery for each one. Doe's anybody have an experience (successful) with such queries?