Re: Sorting CONNECT BY query in Oracle 8.1

Hi Maoz,

I suggest just sorting by LEVEL (or lev in your query) instead of using ROWNUM. I don't know if that'll speed up the query but you can try.

9i allows using CONNECT BY with a join query. So if you upgrade to 9i, you don't need to use a subquery and can do the entire operation in a single select statement.

HTH,
Dave

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
Received on Wed Oct 01 2003 - 14:19:22 CDT