Oracle подключиться, включив критерии остановки

Question

Это стол articles включая иерархические структуры articel. 1 сборка состоит из n компоненты. Таким образом, мы можем просматривать структуру и использование (вверх и вниз) для статьи.

Используя иерархические запросы Oracle, это можно сделать очень эффективно на уровне SQL.

SELECT item
FROM articles
START WITH component = '0815'
CONNECT BY NOCYCLE PRIOR assembly = component;

Представь, что есть статья винт. Этот винт используется во многих сборках и снова в их сборках. Мы хотим выяснить, используется ли гусеница в определенных сборках, обозначенных WHERE пункт несколько уровней выше.

SELECT item
FROM articles
WHERE attr1 = 'marker' --any condition
START WITH component = '0815'
CONNECT BY NOCYCLE PRIOR assembly = component;

Это утверждение прекрасно работает, но в результате оценит все возможные сборки. В нашем случае нас просто интересует, есть ли хотя бы одна сборка, которая соответствует, а не всему результату. Оператор занимает минуты для всех сборок, но может быть значительно быстрее, когда останавливается после первой строки, чтобы ответить на заданный вопрос.

Есть ли способ сказать, что Oracle прерывает этот запрос после первого совпадения?

Answer 1

Вы можете использовать рекурсивный факторинг подзапросов, чтобы остановить весь поиск следующим образом:

with h(it,art,match,anymatch) as
       (select item, assembly
             ,     case when attr1 = 'marker' then 1 else 0 end
             , max(case when attr1 = 'marker' then 1 else 0 end) over()
          from articles
         where component = '0815'
        union all
        select item, assembly
             ,     case when attr1 = 'marker' then 1 else 0 end
             , max(case when attr1 = 'marker' then 1 else 0 end) over()
          from h, articles
         where art = component
           and anymatch = 0)
cycle art set cycle to 1 default 0
select it item
  from h
 where match = 1
   and cycle = 0

Он вернет все совпадения, найденные на минимально возможном уровне.

Однако, поскольку это первый поиск в ширину, он не будет намного быстрее, если первый найден marker глубоко

Изменение состояния anymatch = 0 в match = 0 (anymatch не нужно больше вычислять) остановит поиск только по той ветви, в которой находится совпадение.

Answer 2

Для реального поиска в глубину вы можете использовать следующий PL/SQL:

FUNCTION search(p_component IN VARCHAR2, p_attr1 IN VARCHAR2)
RETURN VARCHAR2 IS
  i VARCHAR2(4000);
BEGIN
  FOR q IN (SELECT * FROM articles WHERE component = p_component)
  LOOP
    IF q.attr1 = p_attr1 THEN
      RETURN q.item;
    END IF;
    i := search(q.assembly, p_attr1);
    IF i IS NOT NULL THEN
      RETURN i;
    END IF;
  END LOOP;
  RETURN NULL;
END;

Вы вызываете функцию следующим образом:

search('0815', 'marker')

Я думаю, что это решение будет намного медленнее, если marker не появляется вообще. Он также не проверяет циклы и работает до ограниченного уровня (предел для открытых курсоров или для стека вызовов может быть исчерпан).

В Oracle 12 вы можете поместить PL / SQL в SQL:

WITH
  FUNCTION search(p_component IN VARCHAR2, p_attr1 IN VARCHAR2)
  RETURN VARCHAR2 IS
    i VARCHAR2(4000);
  BEGIN
    FOR q IN (SELECT * FROM articles WHERE component = p_component)
    LOOP
      IF q.attr1 = p_attr1 THEN
        RETURN q.item;
      END IF;
      i := search(q.assembly, p_attr1);
      IF i IS NOT NULL THEN
        RETURN i;
      END IF;
    END LOOP;
    RETURN NULL;
  END;
SELECT search('0815', 'marker')
  FROM dual