Re: Evaluation of SQL SELECT WHERE's

On Mon, Oct 12, 2020 at 2:01 AM Reeve <rfritchman@xxxxxxxxx> wrote:

...are the clauses tested in order or does the optimizer find one row from
the optimized access path (or through a table scan) regardless of how the
statement is coded and quit?

Yes, Db2 for I does short-circuit evaluation. If the first clause is true,
the Db2 doesn't try to evaluate the other two.

What that means as far as the optimizer I'm not sure.

Other than executing multiple SQL statements, is there a good technique for
controlling the selection process?

Don't try, kind of the point of using SQL is you don't need to worry about
how the Db gets the data.

If performance doesn't meet your needs, you can use VE to see what the Db
is doing and what indexes if any it would like to have.

In this particular case, creating an individual index over each selection
column would likely be useful. Certainly consider an EVI index for
PMP...and possibly EVI's for the other two also given that they likely have
a limited number of distinct values.

Note that you've requested the "first row only", yet there's no order by.
So there's no hard definition of "first", it's whatever the Db happens to
find; could be different on every run. Maybe that's ok...maybe not.

Again, the point is you can't control how the data is retrieved. Best you
can do is try to help the Db and make sure that you're asking correctly.

Charles