Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: hierarchical-Select with sorted Output
Hi,
There are no problems to use hint index_asc in your view. Here is the sample:
SVRMGR> create table test_emp 2> (EMPLOYEE_ID NUMBER(4) 3> ,LAST_NAME VARCHAR2(15) 4> ,FIRST_NAME VARCHAR2(15) 5> ,MANAGER_ID NUMBER(4) 6> ,constraint test_emp_pk primary key (EMPLOYEE_ID) 7> ,constraint test_emp_fk foreign key (MANAGER_ID) references test_emp 8> );
2> test_emp(MANAGER_ID);
Statement processed.
SVRMGR> create index test_emp_manager_id_last_name on
2> test_emp(MANAGER_ID,LAST_NAME,FIRST_NAME);
Statement processed.
SVRMGR> insert into test_emp values(1 ,'IVANOV' ,'IVAN' ,null);
1 row processed.
SVRMGR> insert into test_emp values(2 ,'SIDOROV' ,'SIDOR' ,1);
1 row processed.
SVRMGR> insert into test_emp values(3 ,'RINDIN' ,'ALEXANDER',1);
1 row processed.
SVRMGR> insert into test_emp values(4 ,'PETROV' ,'PETER' ,1);
1 row processed.
SVRMGR> insert into test_emp values(5 ,'VASIN' ,'VASILIY' ,2);
1 row processed.
SVRMGR> insert into test_emp values(6 ,'GRIGORIEV' ,'GRIGORIY' ,2);
1 row processed.
SVRMGR> insert into test_emp values(7 ,'SAMOILOV' ,'SAMUIL' ,2);
1 row processed.
SVRMGR> insert into test_emp values(8 ,'ABRAMOV' ,'ABRAMOV' ,2);
1 row processed.
SVRMGR> insert into test_emp values(9 ,'SERGEEV' ,'SERGEY' ,3);
1 row processed.
SVRMGR> insert into test_emp values(10,'ANDREEV' ,'ANDREY' ,3);
1 row processed.
SVRMGR> insert into test_emp values(11,'ANDREEV' ,'ALEXANDER',3);
1 row processed.
SVRMGR> insert into test_emp values(12,'KAZANOK' ,'DMITRIY' ,4);
1 row processed.
SVRMGR> insert into test_emp values(13,'SAVOSTA' ,'DANIIL' ,4);
1 row processed.
SVRMGR> insert into test_emp values(14,'ANANIEV' ,'PAVEL' ,4);
1 row processed.
SVRMGR> insert into test_emp values(15,'USICHENKO' ,'URIY' ,6);
1 row processed.
SVRMGR> insert into test_emp values(16,'KOSTUK' ,'OLEG' ,6);
1 row processed.
SVRMGR> insert into test_emp values(17,'ONUFRIEV' ,'IVAN' ,6);
1 row processed.
SVRMGR> insert into test_emp values(18,'POGOSYAN' ,'VALERIY' ,12);
1 row processed.
SVRMGR> insert into test_emp values(19,'YAKOVLEV' ,'YAKOV' ,12);
1 row processed.
SVRMGR> insert into test_emp values(20,'VICTOROV' ,'VICTOR' ,12);
1 row processed.
SVRMGR> commit;
Statement processed.
SVRMGR> create or replace view v_test_emp as 2> select --+ INDEX_ASC(test_emp,test_emp_manager_id_last_name) 3> substr(rpad(' ',level)||LAST_NAME||' '||FIRST_NAME,1,40) NAME,EMPLOYEE_ID,MANAGER_ID 4> from test_emp 5> start with MANAGER_ID is null 6> connect by prior EMPLOYEE_ID=MANAGER_ID;Statement processed.
NAME EMPLOYEE_I MANAGER_ID ---------------------------------------- ---------- ---------- IVANOV IVAN 1 PETROV PETER 4 1 ANANIEV PAVEL 14 4 KAZANOK DMITRIY 12 4 POGOSYAN VALERIY 18 12 VICTOROV VICTOR 20 12 YAKOVLEV YAKOV 19 12 SAVOSTA DANIIL 13 4 RINDIN ALEXANDER 3 1 ANDREEV ALEXANDER 11 3 ANDREEV ANDREY 10 3 SERGEEV SERGEY 9 3 SIDOROV SIDOR 2 1 ABRAMOV ABRAMOV 8 2 GRIGORIEV GRIGORIY 6 2 KOSTUK OLEG 16 6 ONUFRIEV IVAN 17 6 USICHENKO URIY 15 6 SAMOILOV SAMUIL 7 2 VASIN VASILIY 5 220 rows selected.
2> select * from v_test_emp;
Statement processed.
SVRMGR> select substr(operation,1,10), 2> substr(options,1,15), 3> substr(object_name,1,25), 4> id,parent_id 5> from plan_table order by statement_id,id; SUBSTR(OPE SUBSTR(OPTIONS, SUBSTR(OBJECT_NAME,1,25) ID PARENT_ID ---------- --------------- ------------------------- ---------- ---------- SELECT STA 0 VIEW V_TEST_EMP 1 0 CONNECT BY 2 1 TABLE ACCE FULL TEST_EMP 3 2 TABLE ACCE BY USER ROWID TEST_EMP 4 2 TABLE ACCE BY INDEX ROWID TEST_EMP 5 2 INDEX RANGE SCAN TEST_EMP_MANAGER_ID_LAST_ 6 57 rows selected.
You can see that proper index is using according to the plan.
Andrew Protasov
> 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 - 06:56:55 CST