Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: hierarchical-Select with sorted Output

Re: hierarchical-Select with sorted Output

From: Andrew Protasov <oracle_at_protasov.kiev.ua>
Date: Mon, 14 Dec 98 14:56:55 +0200
Message-ID: <ADNiGTsK42@protasov.kiev.ua>

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> );

Statement processed.
SVRMGR> create index test_emp_manager_id on

     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.
SVRMGR> select * from v_test_emp;
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          2
20 rows selected.
SVRMGR> delete from plan_table;
0 rows processed.
SVRMGR> explain plan set statement_id='query1' INTO plan_table for

     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          5
7 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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US