How to optimize the SubQuery that returns the same result for each outer select

Question

I'm not expert SQL user so I'm seeing some advice to optimize the below SQL.

SELECT pm.name, pm.WTPARTNUMBER 
FROM WTPARTMASTER pm, WTPART pt, CHECKOUTLINK clink
WHERE pt.IDA2A2 = clink.CLASSNAMEKEYROLEBOBJECTREF
AND pm.WTPARTMASTER
  IN (
    SELECT master.WTPARTNUMBER
    FROM WTPARTMASTER master 
    WHERE master.IDA2A2 
       NOT IN (
          SELECT ulink.IDA3B5
          FROM WTPARTUSAGELINK ulink, wtpart part
          WHERE part.IDA3E2ITERATIONINFO = 1
          AND ulink.CLASSNAMEKEYROLEAOBJECTREF = part.IDA2A2
       )
    AND master.WTPARTNUMBER LIKE '%-R'
  )
AND pm.WTPARTNUMBER LIKE '%-R'
ORDER BY pm.WTPARTNUMBER;

The first inner select returns the around 60K numbers which will be used in outer query IN.

Is there way I can execute the first inner query and use that result in the outer IN operator?

Or any other way to optimize the query?

Thanks in advance.

Answer 1

This is slow:

where somefield not in 
(
select somefield
etc
)

This is faster:

where somefield in 
(
select somefield
from etc
where the conditions match the outer query
minus
select somefield
from etc
where the conditions match the outer query
and you want to exclude them
)
)