Вложенные в Oracle предложения CONNECT BY приводят к снижению производительности

Question

Запрос ниже занимает около минуты. Я полагаю, что низкая производительность вызвана двумя предложениями IN (SELECT...). У меня есть таблица терминов, где один может быть связан с другим через таблицу term_relationship. Эти отношения могут быть рекурсивными, например, тип собаки млекопитающее, млекопитающее - это тип животного. Эта рекурсия может быть любой глубины, но, вероятно, не более ~ 10 уровней. Я пытаюсь выбрать все термины, которые имеют (потенциально рекурсивное) отношение с типом А и имеют (потенциально рекурсивное) отношение с типом B. Я думаю, что замена двух предложений "IN (SELECT..." на ограничения внешнего запроса) повысила бы производительность, но не может понять, как это сделать с помощью предложений CONNECT BY. Кто-нибудь может помочь с этим?

SELECT term_name
FROM term
WHERE term_id IN 
   (SELECT term_id 
    FROM term_relationship 
    START WITH related_term_id = 123
    CONNECT BY NOCYCLE PRIOR term_id = related_term_id)
AND term_id IN 
   (SELECT term_id 
    FROM term_relationship 
    START WITH related_term_id = 456
    CONNECT BY NOCYCLE PRIOR term_id = related_term_id)

Answer 1

Вместо того, чтобы делать то же самое CONNECT BY запросите дважды только с разными начальными значениями, как насчет того, чтобы сделать это один раз, предоставив оба начальных значения одному экземпляру подзапроса. Это изменение даст вам все term_idсвязанные с любым из ваших начальных значений, однако вы хотите только те, term_idсвязаны с обоими вашими начальными значениями. Чтобы получить это, вам нужно сгруппировать результаты по term_id и ограничить их результатами, имеющими более одного:

SELECT term_name
  FROM term
 WHERE term_id IN 
    (SELECT term_id 
       FROM term_relationship 
      START WITH related_term_id in (123, 456)
    CONNECT BY NOCYCLE PRIOR term_id = related_term_id
      group by term_id having count(*) >= 2)

редактировать
С помощью приведенного выше кода я сделал предположение о ваших данных, которые могут быть неверными. Я предположил древовидную структуру, в которой вы начинали с узлов на ветке и двигались вверх к корню, как на диаграмме A, однако, если ваши данные выглядят как диаграмма B, тогда приведенный выше запрос не будет выполнен, если вы начнете с узлов 7 и 9 поскольку узел 7 имеет два пути назад к узлу 1, и вышеупомянутый запрос дважды вернет узел 1, тем самым неправильно идентифицируя его как общий узел.

A)   -(1)-                    B)   -(1)-
    /  |  \     (8)               /  |  \     (8)
  (2)  |  (3)    |              (2)  |  (3)    |
   |  (4)  |    (9)              |  (4)  |    (9)
  (5)     (6)                   (5)     (6)
           |                      \     /
          (7)                      -(7)-

Приведенный ниже запрос исправляет это и правильно идентифицирует, что для начальных узлов 7 и 9 нет общих узлов, однако с начальными узлами 7 и 4 узел 1 идентифицируется как общий узел:

SELECT term_name
  FROM term
 WHERE term_id IN 
    (SELECT term_id 
     FROM term_relationship 
    START WITH related_term_id in (123, 456)
  CONNECT BY NOCYCLE PRIOR term_id = related_term_id
    group by term_id
   having count(distinct connect_by_root related_term_id) >= 2)