Re: Another Hierarchical Query Question

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: 19 Oct 1999 16:28:05 GMT
Message-ID: <7ui66l$s89$1@news.seed.net.tw>

<yitbsal_at_statcan.ca> wrote in message news:7ufdh2$kt5$1_at_nnrp1.deja.com...
> Hi,
>
> I store an ordered tree of the following form in a table:
>
> 1
> 5 2 44 12
> 3 4 6 8 9 10 11 13 14
>
> where 1 is the parent of 5, 2, 44 and 12, 5 is the parent of 3 and 4, 2
> is the parent of 6, and so on.
>
> The tree is stored is a table MYTABLE as follows:
>
> LOCAL_ID,PARENT_ID,LEFT_SIBLING
> 1
> 5 1
> 3 5
> 4 5 3
> 6 2
> 44 1 2
> 8 44
> 9 44 8
> 10 44 9
> 11 44 10
> 12 1 44
> 13 12
> 14 12 13
>
> Note that the order of the siblings, as defined by the LEFT_SIBLING
> column, is important. The order is NOT defined by the id number itself
> (ie. note that 44 comes before 12).
>
> Now the following query:
>
> SELECT
> LPAD('--------',2*(LEVEL-1)) || LOCAL_ID AS L_ID, PARENT_ID,
> LPAD('--------',2*(LEVEL-1)) || LEFT_SIBLING AS LEFT_SIB
> FROM MYTABLE
> START WITH LOCAL_ID = 1
> CONNECT BY PARENT_ID = PRIOR LOCAL_ID;
>
> results in:
>
> LOCAL_ID PARENT_ID LEFT_SIBLING
> 1
> --5 1 --
> ----3 5 ----
> ----4 5 ----3
> --44 1 --2
> ----8 44 ----
> ----9 44 ----8
> ----10 44 ----9
> ----11 44 ----10
> --12 1 --44
> ----13 12 ----
> ----14 12 ----13
> --2 1 --5
> ----6 2 ----
>
> Instead of this, I'd like to get a result as follows:
>
> LOCAL_ID PARENT_ID LEFT_SIBLING
> 1
> --5 1 --
> ----3 5 ----
> ----4 5 ----3
> --2 1 --5
> ----6 2 ----
> --44 1 --2
> ----8 44 ----
> ----9 44 ----8
> ----10 44 ----9
> ----11 44 ----10
> --12 1 --44
> ----13 12 ----
> ----14 12 ----13
>
> Note that this is simply a left depth-first traversal of the tree.

Your table design is not suitable for hierarchical query. Since there are two-way hierarchies,
and Oracle does not support nested hierarchical query. The table needs another column to store the sibling order. Moreover, it needs a trick to access rows by the order of siblings. See the following:

SQL> create table mytable
  2 (local_id number, parent_id number, left_sibling number);

Table created.

SQL> insert into mytable values(1, null, null);

1 row created.

SQL> insert into mytable values(2, 1, 5);

1 row created.

SQL> insert into mytable values(3, 5, null);

1 row created.

SQL> insert into mytable values(4, 5, 3);

1 row created.

SQL> insert into mytable values(5, 1, null);

1 row created.

SQL> insert into mytable values(6, 2, null);

1 row created.

SQL> insert into mytable values(8, 44, null);

1 row created.

SQL> insert into mytable values(9, 44, 8);

1 row created.

SQL> insert into mytable values(10, 44, 9);

1 row created.

SQL> insert into mytable values(11, 44, 10);

1 row created.

SQL> insert into mytable values(12, 1, 44);

1 row created.

SQL> insert into mytable values(13, 12, null);

1 row created.

SQL> insert into mytable values(14, 12, 13);

1 row created.

SQL> insert into mytable values(44, 1, 2);

1 row created.

SQL> alter table mytable add lvl number;

Table altered.

SQL> update mytable m
  2 set lvl=

  3      (select new_lvl
  4        from (select local_id, level as new_lvl
  5               from mytable
  6               start with left_sibling is null
  7               connect by prior local_id=left_sibling)
  8        where local_id=m.local_id);

14 rows updated.

SQL> create index ind_mytabl_lvl on mytable (lvl);

Index created.

SQL> column local_id format a10
SQL> column left_sibling format a10

SQL> -- Here is the primitive result:
SQL> select

  2      lpad('-', 2*(level-1), '-')||local_id as local_id, parent_id,
  3      lpad('-', 2*(level-1), '-')||left_sibling as left_sibling

  4 from mytable
  5 start with local_id=1
  6 connect by prior local_id=parent_id   7 /

LOCAL_ID PARENT_ID LEFT_SIBLI
---------- --------- ----------
1

--2                1 --5
----6              2 ----
--5                1 --
----3              5 ----
----4              5 ----3
--12               1 --44
----13            12 ----
----14            12 ----13
--44               1 --2
----8             44 ----
----9             44 ----8
----10            44 ----9
----11            44 ----10

14 rows selected.

SQL> -- Here is the expectant result:

SQL> select /*+ index(mytable ind_mytable_lvl) */
  2      lpad('-', 2*(level-1), '-')||local_id as local_id, parent_id,
  3      lpad('-', 2*(level-1), '-')||left_sibling as left_sibling

  4 from mytable
  5 start with local_id=1
  6 connect by prior local_id=parent_id   7 and lvl>0;

LOCAL_ID PARENT_ID LEFT_SIBLI
---------- --------- ----------
1

--5                1 --
----3              5 ----
----4              5 ----3
--2                1 --5
----6              2 ----
--44               1 --2
----8             44 ----
----9             44 ----8
----10            44 ----9
----11            44 ----10
--12               1 --44
----13            12 ----
----14            12 ----13

14 rows selected. Received on Tue Oct 19 1999 - 11:28:05 CDT