Sorting CONNECT BY query in Oracle 8.1

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 - 07:18:19 CDT