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