Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

Hierarchy with mother and father

alinuxMay 29 2014 — edited Jul 9 2014

Hi Guys,

I was trying to write a query to return the entire genalogy of an ID.

we have the folloowing configuration:

[Code]

create table genealogy

(id varchar2 (10),

m_id varchar2 (10),

f_id varchar2 (10)

);

create unique index id_idx on genealogy (id);

create unique index m_id_idx on genealogy (m_id);

create unique index f_id_idx on genealogy (f_id);

insert into genealogy (id, m_id, f_id) values  (0,1,2);

insert into genealogy (id, m_id, f_id) values  (1,3,4);

insert into genealogy (id, m_id, f_id) values  (2,5,6);

insert into genealogy (id, m_id, f_id) values  (3,7,8);

insert into genealogy (id, m_id, f_id) values  (4,9,10);

insert into genealogy (id, m_id, f_id) values  (5,12,12);

insert into genealogy (id, m_id, f_id) values  (6,14,14);

insert into genealogy (id, m_id, f_id) values  (7,15,16);

insert into genealogy (id, m_id, f_id) values  (8,17,18);

insert into genealogy (id, m_id, f_id) values  (9,19,20);

insert into genealogy (id, m_id, f_id) values  (10,21,22);

insert into genealogy (id, m_id, f_id) values  (11,23,24);

insert into genealogy (id, m_id, f_id) values  (12,25,26);

insert into genealogy (id, m_id, f_id) values  (13,27,28);

insert into genealogy (id, m_id, f_id) values  (14,29,30);

insert into genealogy (id, m_id, f_id) values  (15,NULl,NULL);

insert into genealogy (id, m_id, f_id) values  (16,NULl,NULL);

insert into genealogy (id, m_id, f_id) values  (17,NULl,NULL);

insert into genealogy (id, m_id, f_id) values  (18,NULl,NULL);

insert into genealogy (id, m_id, f_id) values  (19,NULl,NULL);

insert into genealogy (id, m_id, f_id) values  (20,NULl,NULL);

insert into genealogy (id, m_id, f_id) values  (21,NULl,NULL);

insert into genealogy (id, m_id, f_id) values  (22,NULl,NULL);

insert into genealogy (id, m_id, f_id) values  (23,NULl,NULL);

insert into genealogy (id, m_id, f_id) values  (24,NULl,NULL);

insert into genealogy (id, m_id, f_id) values  (25,NULl,NULL);

insert into genealogy (id, m_id, f_id) values  (26,NULl,NULL);

insert into genealogy (id, m_id, f_id) values  (27,NULl,NULL);

insert into genealogy (id, m_id, f_id) values  (28,NULl,NULL);

insert into genealogy (id, m_id, f_id) values  (29,NULl,NULL);

insert into genealogy (id, m_id, f_id) values  (30,NULl,NULL);

BEGIN

   FOR I IN 31..50000 LOOP

      INSERT INTO genealogy (id, m_id, f_id) VALUES (I,NULL,NULL);

   END LOOP;

END;

BEGIN

   DBMS_STATS.GATHER_TABLE_STATS(OWNNAME          => USER,

                                 TABNAME          => 'GENEALOGY',

                                 METHOD_OPT       => 'for all indexed columns size auto');

END;

SELECT ID,m_id,F_id, LEVEL

FROM   genealogy where level <=4

START  WITH ID = '0'

CONNECT BY PRIOR M_ID = ID OR

           PRIOR F_ID = ID

[/code]

I want the relatives till grandparents.

The issue is that because of the OR condition the execution plan is using a full table scan. If I remove that it is using multipel unique access to get the list.

but when I add both mother and father it is using Full. I imagine this SQL accessing the table 15 times with an unique index access. That would be way better than a FTS.

Do you have any ideas?

Thank you

This post has been answered by Solomon Yakobson on May 30 2014
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 6 2014
Added on May 29 2014
9 comments
397 views