3 ms·

Oracle, at least, has/had the problem that it can't use the values of parameters to affect the execution plan. So sometimes it's good to write in the values in

Oracle, at least, has/had the problem that it can't use the values of parameters to affect the execution plan. So sometimes it's good to write in the values in the SQL statement. If you write "select .. where account_id=?" then the query plan will probably be the same for any account_id, e.g. use the index, so that's fine, working as intended. However, if you write "... where active=?" and active can only be true or false and there are a few thousand active entries but tens of millions of inactive ones, the query plan might be quite different for active=true and active=false. (Active=true would use an index, but active=false would make more sense to just sequentially read the whole table, as most rows will be used anyway.) So if you have very few distinct values, the selection of the value influences the execution plan, and the value doesn't come from an untrusted source, it can be better to not use query parameters. That is to say, 99% of the time you'll want to use query parameters, but rejecting queries which don't use them isn't a solution either, as you do need to avoid them sometimes, unfortunately.

speedplane 7y ago
> 99% of the time you'll want to use query parameters, but rejecting queries which don't use them isn't a solution either, as you do need to avoid them sometimes, unfortunately This is one of the problems that I have with most SQL databases: they try to do the magic for you and make it hard to see what's going on under the hood. Databases aren't magic, they are just indexes, and there are only so many ways of organizing them. Providing a bit more transparency into how the data is stored and retrieved allow you to write efficient queries without the guesswork.
hinkley 7y ago
In that case, would you not use a partial index, and kill two birds with one stone? On that same project we got into putting hints into the queries. I’m sure if push came to shove you could do that for situations like this one and solve the problem. Less portable that way, of course.
cneu 7y ago
That's not entirely correct. A hard parse causes the optimizer to do bind variable peeking, i.e. it chooses the execution plan based on the bind variable value. https://docs.oracle.com/cd/E11882_01/server.112/e41573/optimops.htm#PFGRF94588 https://docs.oracle.com/cd/E11882_01/server.112/e41573/optim...
- pif 7y ago
  From the same source: When choosing a plan, the optimizer only peeks at the bind value during the hard parse. This plan may not be optimal for all possible values.

speedplane 7y ago

> 99% of the time you'll want to use query parameters, but rejecting queries which don't use them isn't a solution either, as you do need to avoid them sometimes, unfortunately This is one of the problems that I have with most SQL databases: they try to do the magic for you and make it hard to see what's going on under the hood. Databases aren't magic, they are just indexes, and there are only so many ways of organizing them. Providing a bit more transparency into how the data is stored and retrieved allow you to write efficient queries without the guesswork.

hinkley 7y ago

In that case, would you not use a partial index, and kill two birds with one stone? On that same project we got into putting hints into the queries. I’m sure if push came to shove you could do that for situations like this one and solve the problem. Less portable that way, of course.

cneu 7y ago

That's not entirely correct. A hard parse causes the optimizer to do bind variable peeking, i.e. it chooses the execution plan based on the bind variable value. https://docs.oracle.com/cd/E11882_01/server.112/e41573/optimops.htm#PFGRF94588 https://docs.oracle.com/cd/E11882_01/server.112/e41573/optim...

pif 7y ago
From the same source: When choosing a plan, the optimizer only peeks at the bind value during the hard parse. This plan may not be optimal for all possible values.