1

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?

10
  • Updated the wording - my mistake if I was unclear. Commented Jul 19, 2016 at 13:13
  • Updated my unclear question again. Hopefully it's a better description of what I'm trying to do. Commented Jul 19, 2016 at 13:17
  • 1
    To clarify, in your actual query the DUAL d is an actual table or view and the three EXISTS predicates are correlated to it via one of its columns, and it's the same column in all three cases, correct?
    – Andriy M
    Commented Jul 19, 2016 at 13:33
  • in your actual query the DUAL d is an actual table or view and the three EXISTS predicates are correlated to it via one of its columns, and it's the same column in all three cases Mostly correct - I just updated the question. Commented Jul 19, 2016 at 13:37
  • 1
    I've edited your question (and hopefully fixed some mismatching parentheses near the EXISTS / OR subqueries) Commented Jul 19, 2016 at 14:38

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.