hierarchical-Select with sorted Output

We have in our DB-schema a table like the EMP-table with hierarchical information. In our select with the connect by prior it is importent to get the response in sorted order within a hierarchical step. To get this response we created an index on the join-column an the sort-column. That worked until I had to create an Index only on the join-column for better performance of other selects (that select don't use the concatenated index). Now the hierarchical select use the new index and so I don't get the sortorder, that I want. I tried to use Hints but it doesn't work, perhaps because we use the hierarchical select in our applications not direct, we use it in a view. For us is the sorted order and the performance of the response very importend. I hope it is clear what I want to say. My english is not so got. Please excuse me. I would be very glad, if someone can help me to found a solution.

The Select:
  select LPAD(' ',2*(LEVEL-1)) || last_name org_chart   FROM emp
  START WITH manager_id is NULL
   CONNECT BY PRIOR employee_id = manager_id

The EMP-Table

Name                            Null?    Type
------------------------------- -------- ----
EMPLOYEE_ID                              NUMBER(4)
LAST_NAME                                VARCHAR2(15)
FIRST_NAME                               VARCHAR2(15)
MIDDLE_INITIAL                           VARCHAR2(1)
JOB_ID                                   NUMBER(3)
MANAGER_ID                               NUMBER(4)
HIRE_DATE                                DATE
SALARY                                   NUMBER(7,2)
COMMISSION                               NUMBER(7,2)
DEPARTMENT_ID                            NUMBER(2)

The Indizes

SQL> create index i1 on emp (employee_id);
SQL> create index i2 on emp(manager_id);
SQL> create index i3 on emp(manager_id, last_name);

The normal Output			 The sorted Output (that is what we want)
KING							 KING
  DOYLE					               ALBERTS
    PETERS						     FISHER
    SHAW						       DOUGLAS
    PORTER						     ROBERTS
    ROSS						   BAKER
    JENSEN						     LEWIS
  DENNIS						     SOMMERS
    DUNCAN						   BLAKE
    LANGE						     ALLEN
    WEST						     JAMES
    MURRAY						     MARTIN
  BAKER						     TURNER
    LEWIS						     WARD
    SOMMERS						   CLARK
  JONES						     MILLER
    SCOTT						   DENNIS
      ADAMS						     DUNCAN
    FORD						     LANGE
      SMITH						     MURRAY
  ALBERTS						     WEST
    FISHER						   DOYLE
      DOUGLAS					                 JENSEN
    ROBERTS						     PETERS
  BLAKE						     PORTER
    ALLEN						     ROSS
    WARD						     SHAW
    MARTIN						   JONES
    TURNER						     FORD
    JAMES						       SMITH
  CLARK						     SCOTT
    MILLER  					                   ADAMS

We use RDBMS 7.3.2
and the Cost-Base Optimizer

 Our Table has 10000 rows

Please sent me also an eMail because I can't read the newsgroup all time.

Thank you for any hints,
with kind regards

--
Heike Becker
becker_at_duf.de Received on Mon Dec 14 1998 - 03:44:08 CST