0

I know doing an explain plan on the 2 queries below will yield the same cost, but will the database evaluate both parts of an OR condition or will it stop after the first part that evaluates to true?

select 1 from dual where
exists (select 1 from small_table where col1 = 10)
or
exists (select 1 from big_table where col1 = 10);

Swap the 2 exists conditions:

select 1 from dual where
exists (select 1 from big_table where col1 = 10)
or
exists (select 1 from small_table where col1 = 10);

An example of why this would matter:

Say one of the subqueries uses a table with millions of records (big_table) and the other part uses a table with only a couple hundred records (small_table). I know that the record I'm searching for is more likely to be in small_table, but could sometimes be in big_table. In this case, I would want to order the OR condition with the small_table first. If this were java...

String a = "small";
if (a == "small" || a == "big") {
    // a == "big" won't get executed
}
Matt
  • 828
  • 8
  • 25
  • The database is free to run those expressions in any order it likes. But with the `EXISTS` operator it doesn't really matter that much as the database will stop after the first row it finds - and if the column in the `where` condition is indexed that is a pretty efficient lookup –  Dec 14 '16 at 22:18
  • It very much depends on statistics and information stored in the tables. The optimizer might consider from statistics to do a lookup only on the `small_table`, if he has cardinality information (in the form of statistics) about that value in `small_table`. Otherwise, if he doesn't have information about this, then it will do a lookup on `big_table`, or it will do a lookup in both tables (sounds crazy, maybe) if there are no reliable statistics to point to which table (small or big) might have the value that you're checking for. – Radu Gheorghiu Dec 14 '16 at 22:18
  • Since this question is closed because it is a duplicate, I posted an answer in the "old thread" (linked to at the top). In short: if you need to be 100% sure of the order of evaluation, you can rewrite your condition using a tool that guarantees order of evaluation and short-circuiting. I give an example of rewriting an OR condition with a CASE expression (see the old thread). –  Dec 14 '16 at 23:03

0 Answers0