Check if 1/3 Queries Evaluates to True

oracle

My large (+100 line) query contains a WHERE filter similar to:

select * 
from (<complex 100+ line query>) d 
WHERE (
   (EXISTS (select 1 FROM TABLE1 t1 
            WHERE d.dummy = t1.x 
              AND t1.col1 = 'FOOBAR' AND t1.col2 IN ('A', ))) OR 
   (EXISTS (select 1 FROM TABLE2 t2 
            WHERE d.dummy = t2.x 
              AND t2.col1 = 'FOOBAR' AND t2.col2 IN ('A', 'B'))) OR
   (EXISTS (select 1 FROM TABLE3 t3 
            WHERE d.dummy = t3.x 
              AND t3.col1 = 'FOOBAR' AND t3.col2 IN ('A', 'B')))
)

Prior to this "type" of change, i.e. 3 EXISTS, the query ran in 1-3 seconds. However, with the above "type" of addition (as a WHERE filter, the query has ground to a halt – 60 seconds.

I've looked at the EXPLAIN PLAN, but I don't understand the output.

Basically, I only want to show the row from <query> d if its dummy column matches one of the x columns of TABLE1, TABLE2, or TABLE3.

From a performance and good SQL practice point-of-view, is the above WHERE inner query a reasonable way to determine if at least 1/3 conditions is true?

Best Answer

Make sure t3.x is indexed.

If you know one test is much cheaper than another, it may be faster to JOIN them in a manner that makes sense, then use the /*+ORDERED*/ hint to force the optimizer to join them in the order you've written them. This is particularly necessary if your inner query contains REGEX expressions since the optimizer prefers to procrastinate evaluating them.

Related Question