sql - sys_connect_by_path - starts with oracle

Oracle 10g Connect By Prior-Problemas de rendimiento

Question

Tengo la siguiente declaración SQL:

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))

Esto en una tabla con aproximadamente 1,6 millones de animales. Cada registro tiene ID de animal, ID de animal de Sire y Dam_Animal_Id (Sire = Father, Dam = Mother).

Uso este sql para mostrar el pedigrí completo de los animales. Los resultados mostrarán Animal, 2 padres, 4 abuelos, etc.

Mi problema es que esta afirmación toma 15 segundos, para un animal. Tiene que haber una forma de optimizar esto. ¿Alguna idea?

Answer 1

No he tenido mucho tiempo para probar esto, así que hay un poco de DYOR en la respuesta, pero ¿sería útil usar una vista en línea?

Como no ha publicado un plan explicativo, no puedo evitarlo demasiado, me temo y en la siguiente solución, puede encontrar que la unión en la cláusula WITH le causa problemas de rendimiento, pero puede ayudarlo en su camino hacia una solución.

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 )

Answer 2

¿Hay índices en sire_animal_id y dam_animal_id? Podría estar haciendo escaneos de tabla completa.

Answer 3

Sí, hay índices.

Answer 4

Traté de volver a crear tu situación y no pude lograr que Oracle usara los índices sabiamente. Estoy seguro de que hay una forma inteligente de hacerlo. Pero si nadie más puede entenderlo, abajo está la manera tonta y fea.

Como solo obtiene una cierta cantidad de niveles, puede crear una conexión manualmente. Obtener el primer nivel, unirlo al segundo nivel (que obtiene resultados de una copia de la primera consulta), unirlo al tercer nivel (que obtiene resultados de una copia de la segunda consulta), etc. Solo hice tres niveles aquí, pero puedes copiar y pegar para hacer el cuarto. Es más difícil de usar ya que la identificación original se repite tantas veces, pero es súper rápida (0,005 segundos en mi máquina con 1,6 millones de registros).

--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'')
);