Maoz Mussel wrote:
>Hi,
>
>I'm using Oracle 8.1.7: I'm trying to proces join and CONNECT BY
>within the same query ('simple' format of such thing is not allowed
>with Oracle). Since I want to keep the order of the original connect
>by query (its default is in hierarchy order from top to bottom), I
>added the ROWNUM in the inside query, and used its value in the ORDER
>BY of the 'main' query. This way I get the required result, however
>the execution time is too long - for a table(s) with 1000-2000
>records, the execution time with the ORDER BY clause is 3 sec.,
>compare to 340 Msec without this clause.
>
>The query I used is:
>
> SELECT e.*,
> eh.order_num,
> eh.parent_id,
> eh.lev
> FROM employees e,
> (SELECT ROWNUM row_number, parent_id, order_num,
>LEVEL-1 lev
> FROM employee_hierarchy
> CONNECT BY PRIOR emp_id = parent_emp_id
> START WITH emp_id = 2) eh
> AND e.emp_id = eh.emp_id
> ORDER BY row_number;
>
>Any idea how to get similar results in more efficient way?
>
>thanks,
>Maoz
>
>
Joe Celko has published a book titled "SQL For Smarties" IIRC. Also
check c.d.ms-sqlserver at google.com and search for
postings by Celko on the subject of CONNECT BY.
--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Wed Oct 01 2003 - 08:49:46 CDT
