Skip to Main Content

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

Comments
Post Details
Added on May 29 2014
9 comments
306 views