Oracle 10g Connect By Prior - Performance Issues

Question

I have the following SQL statement:

SELECT 
    CONNECT_BY_ROOT ANIMAL_ID "ORIGINAL_ANIMAL" ,
    ANIMAL_ID, LINE_ID, SIRE_ANIMAL_ID, DAM_ANIMAL_ID,
    LEVEL -1 "LEVEL" FROM ANIMALS 
START WITH ANIMAL_ID IN( '2360000002558' )
CONNECT BY
  ((PRIOR SIRE_ANIMAL_ID = ANIMAL_ID and LEVEL < 5) OR (PRIOR DAM_ANIMAL_ID = ANIMAL_ID AND LEVEL < 5))

This in in a table with about 1.6 Million animals. Each record has Animal_Id, Sire_Animal_Id, and Dam_Animal_Id (Sire = Father, Dam = Mother).

I use this sql to display the full animal pedigree. Results Will show Animal, 2 Parent, 4 GrandParents, etc.

My issue is that this statement takes 15 seconds, for one animal. There has got to be a way to optimize this. Any thoughts?

Answer 1

Are there indexes on sire_animal_id and dam_animal_id? It might be doing full table scans.

Answer 2

Yes, there are indexes.

Answer 3

I tried recreating your situation and I wasn't able to get Oracle to use the indexes wisely. I'm sure there's some smart way to do it. But if no one else here can figure it out, below is the dumb, ugly way.

Since you're only getting a certain number of levels you can manually create a connect by. Get the first level, union that to the second level (which gets results from a copy of the first query), union that to the third level (which gets results from a copy of the second query), etc. I only did three levels here, but you can copy and paste to make the fourth. It's harder to use since the original id is repeated so many times, but it's super fast (0.005 seconds on my machine with 1.6 million records.)

--Original animal
select '101' original_animal, animal_id, line_id, sire_animal_id, dam_animal_id, 0 "level" from animals where animal_id = '101'
union all
--Parents
select '101' original_animal, animal_id, line_id, sire_animal_id, dam_animal_id, 1 "level" from animals
where animal_id = (select sire_animal_id from animals where animal_id = '101')
union all
select '101' original_animal, animal_id, line_id, sire_animal_id, dam_animal_id, 1 "level" from animals
where animal_id = (select dam_animal_id from animals where animal_id = '101')
union all
--Grand parents
select '101' original_animal, animal_id, line_id, sire_animal_id, dam_animal_id, 2 "level" from animals
where animal_id =
(
  select sire_animal_id from animals
  where animal_id = (select sire_animal_id from animals where animal_id = '101')
)
union all
select '101' original_animal, animal_id, line_id, sire_animal_id, dam_animal_id, 2 "level" from animals
where animal_id =
(
  select dam_animal_id from animals
  where animal_id = (select sire_animal_id from animals where animal_id = '101')
)
union all
select '101' original_animal, animal_id, line_id, sire_animal_id, dam_animal_id, 2 "level" from animals
where animal_id =
(
  select sire_animal_id from animals
  where animal_id = (select dam_animal_id from animals where animal_id = '101')
)
union all
select '101' original_animal, animal_id, line_id, sire_animal_id, dam_animal_id, 2 "level" from animals
where animal_id =
(
  select dam_animal_id from animals
  where animal_id = (select dam_animal_id from animals where animal_id = '101')
);