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?
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.