How does Oracle perform OR condition validation?

Question

In Java, a logical OR condition behaves such that if the first condition is true then it does not evaluate the second condition.

For example:

int a = 10;
if (a == 10 || a == 0) {
    // Logic
}

Java does not evaluate the second test (a == 0) because the first condition (a == 10) is true.

If we have an Oracle SQL statement like this:

select * from student where city = :city and 
    (:age is null or age > :age)

How are (age > :age or :age is null) evaluated? If the parameter :age is NULL, then does it evaluate the second condition as well?

Answer 1

The database cost optimizer will consider many factors in structuring the execution of a query. Probably the most important will be the existence of indexes on the columns in question. It will decide the order based on the selectivity of the test and could perform them in different order at different times. Since SQL is a declarative and not procedural language, you cannot generally control the way in which these conditions are evaluated.

There may be some "hints" you can provide to suggest a specific execution order, but you risk adversely affecting performance.

Answer 2

PL/SQL

In PL/SQL, Oracle OR is another example of short circuit evaluation. Oracle PL/SQL Language Fundamentals says (in part)

Short-Circuit Evaluation

When evaluating a logical expression, PL/SQL uses short-circuit evaluation. That is, PL/SQL stops evaluating the expression as soon as it can determine the result. Therefore, you can write expressions that might otherwise cause errors.

SQL

However, in regular SQL, the OR might be evaluated in either order. As pointed out by @JonHeller in his comment the expressions in this question are safe, more caution would be required if dealing with potential division by 0.