Oracle/general sql - are all where conditions always checked?

user_138006 0
Viewed 54

There's a query which does something like this:

select * from cars c where c.WheelCount > 0 or CountWheels(c) > 0

Is the function CountWheels called for every row, or just for those rows where t.Count <= 0 ?

The thing is that that function contains logic that should be executed every time -- very likely a bad design; it's part of code of which I have very little control.

This is a simplified example, and I cannot just write select * from cars c where CountWheels(c.Body) > 0 since that would remove some data. Think of it as: give me all cars that ever had wheels, even though some of them may have 0 wheels right now.

sql-function case where-clause oracle sql
1 Answers

Is the function CountWheels called for every row, or just for those rows where t.Count <= 0 ?

In general, you don’t get to choose this. SQL is a descriptive language, as opposed to a procedural language: you specify the results you want, and you trust the optimizer to pick the best possible execution plan.

You might try to influence the database with a case expression, which gives more control over the execution flow:

where case 
    when c.WheelCount   > 0 then 1
    when CountWheels(c) > 0 then 1
end = 1
user_106767160
Related