Contact Oracle 10g for Priority Issues
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 is a table with about 1.6 million animals. Each entry has Animal_Id, Sire_Animal_Id and Dam_Animal_Id (Sire = Father, Dam = Mother).
I am using this sql to display full animal breed. Results will show Animal, 2 Parents, 4 GrandParents, etc.
My problem is that this expression takes 15 seconds for one animal. There must be a way to optimize this. Any thoughts?
source to share
I tried to recreate your situation and I was unable to get Oracle to use indexes wisely. I'm sure there is some clever way to do this. But if no one else can figure it out, below is the dumb, ugly way.
Since you only get a certain number of levels, you can manually create a connection. Get the first level, merge it with the second level (which gets the results from the copy of the first query), merge it into the third level (which gets the results from the copy of the second query), etc. I've only made three levels here, but you can copy and paste to make the fourth. Harder to use as the original ID is repeated so many times, but it is very fast (0.005 seconds on my machine with 1.6 million entries.)
--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')
);
source to share
I didn't have a lot of time to test this, so there is a bit of DYOR in the answer, but with the inline help view?
Since you haven't posted the plan of explanation, I can't be too scared, and in the solution below, you might find that the join in the WITH clause causes performance issues, but may help you on your way to a solution.
WITH ani
AS (SELECT animal_id,
line_id,
sire_animal_id,
dam_animal_id,
sire_animal_id AS generic_id
FROM animals
UNION
SELECT animal_id,
line_id,
sire_animal_id,
dam_animal_id,
dam_animal_id AS generic_id
FROM animals)
SELECT CONNECT_BY_ROOT animal_id "ORIGINAL_ANIMAL",
animal_id,
line_id,
sire_animal_id,
dam_animal_id,
LEVEL - 1 "LEVEL"
FROM ani
START WITH animal_id = '2360000002558'
CONNECT BY (PRIOR generic_id = animal_id AND LEVEL < 5 )
source to share