Oracle connect by including a stop criteria

Question

There is a table articles including hierarchical articel structures. 1 assembly consists out of n components. So we are able to browse the structure and usages (up and down) for an article.

Using Oracles hierarchical queries this can be done very efficient on sql level.

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

Imagine there is an article screw. This screw is used in lots of assemblies and again their assemblies. We want to figure out if the srew is used in specific assemblies identified by a WHERE clause several levels above.

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

This statement works great, but will evaluate all possible assemblies in the result. In our case we are just interested in if there is at least one assembly which matches and not in the whole result. The statement takes minutes for all assemblies but could be sigificant faster when it stops after the first row to answer the given question.

Is there a way to tell Oracle aborting this query after the first match?

Answer 1

You can use Recursive subquery factoring to stop all searching like this:

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

It will return all matches that are found on a smallest possible level.

However as it is breadth first search, it will not be much faster if the first found marker is deep.

Changing condition anymatch = 0 to match = 0 (anymatch would not need to be calculated anymore) would stop only searching down the branch the match is on.

Answer 2

To do a real depth-first search you can use a following 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;

You call the function like this:

search('0815', 'marker')

My guess is that this solution will be much slower if the marker does not appear at all. It also does not check for cycles, and works up to a limited level (a limit for open cursors or for the call stack can be exhausted).

In Oracle 12 you can put PL/SQL inside 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