Oracle 10g Connect By Prior - проблемы с производительностью

Question

У меня есть следующий оператор 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))

Это в таблице около 1,6 миллиона животных. Каждая запись имеет Animal_Id, Sire_Animal_Id и Dam_Animal_Id (Sire = Отец, Dam = Мать).

Я использую этот sql для отображения полной родословной животных. Результаты покажут Animal, 2 Parent, 4 GrandParents и т.д.

Моя проблема в том, что это утверждение занимает 15 секунд, для одного животного. Должен быть способ оптимизировать это. Какие-нибудь мысли?

Answer 1

Я попытался воссоздать вашу ситуацию и не смог заставить Oracle разумно использовать индексы. Я уверен, что есть какой-то умный способ сделать это. Но если никто здесь не может понять это, ниже - глупый, уродливый путь.

Поскольку вы получаете только определенное количество уровней, вы можете вручную создать соединение. Получить первый уровень, объединить его на второй уровень (который получает результаты от копии первого запроса), объединить его на третий уровень (который получает результаты от копии второго запроса) и т. Д. Я сделал только три уровня здесь, но вы можете скопировать и вставить, чтобы сделать четвертый. Его сложнее использовать, поскольку исходный идентификатор повторяется очень много раз, но он очень быстрый (0,005 секунды на моей машине с 1,6 миллионами записей).

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

Answer 2

У меня не было долгого времени, чтобы проверить это, так что в ответе есть немного DYOR, но поможет ли использование встроенного просмотра?

Я не боюсь, что вы не опубликовали план объяснения, поэтому я боюсь, что в приведенном ниже решении вы можете обнаружить, что объединение в предложении WITH вызывает проблемы с производительностью, но может помочь вам на пути к решение.

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 3

Есть ли индексы для sire_animal_id и dam_animal_id? Это может быть полное сканирование таблицы.

Answer 4

Да, есть индексы.