CODEWITHSUNDEEP
sqloracle-databaselogical-operators
Chamly Idunil
Chamly Idunil

Reputation: 1872

How does Oracle perform OR condition validation?

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?

Upvotes: 6

Views: 3405

Answers (3)

Elliott Frisch
Elliott Frisch

Reputation: 201447

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.

Upvotes: 2

Mathias Magnusson
Mathias Magnusson

Reputation: 197

Let Oracle decide for you. It will most of the time make a much better decision. In this case, there is even a construct that combines test for null with testing a value.

Replace

:age is null or age > :age

With

age > nvl(:age, age - 1)

Upvotes: 1

Jim Garrison
Jim Garrison

Reputation: 86774

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.

Upvotes: 7

Related Questions