2

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
avance70
  • 787
  • 1
  • 11
  • 22
  • Oracle will *probably* call the function only on rows where `WheelCount <= 0` or `NULL`. It has a pretty good optimizer. – Gordon Linoff Aug 13 '20 at 10:29

1 Answers1

6

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
GMB
  • 216,147
  • 25
  • 84
  • 135
  • 2
    "that function contains logic that should be executed every time" - so as case short-circuits, don't you need the searched conditions in reverse order? – Alex Poole Aug 13 '20 at 10:20
  • Thanks for the answer GMB! And thanks for the reply @alex-poole because these two I can do in my environment, and the problem is resolved! – avance70 Aug 13 '20 at 13:59