Search code examples
sqloracle-databasequery-performancemaximooracle18c

Select where: classification and its children

I have a query where I select workorders where the classification = 60280 or where the classification is a child of 60280:

select
    wo.*
from
    workorder wo
where
    exists (select 1 from classancestor where ((ancestor = '60280')) and (classstructureid=wo.classstructureid))

(For what it's worth, this is similar to how the Advanced Search window functions in the Maximo List View.)

While the query above does work fine, I suspect that it won't scale well on a large table, due to the subquery.

Is there a way to select a classification and its children without using a subquery?

Solution 
  • select
    wo.*
from
    workorder wo join classancestor ca on (ca.classstructureid=wo.classstructureid)
where
    ca.ancestor = '60280'

    But this is not necessarily better because Oracle will optimize as it sees fit. Try running it a couple of times to see what happens.