Scenario for using a constant instead of a bind variable in an OLTP system

In an OLTP application, one should be aiming at the usage of bind variables in majority of the scenarios and thus using constant values won’t be a good idea. In a transaction system, we execute hundreds/thousands of queries per second. Therefore we MUST use bind variables and reduce the number of parses as much as possible. OLAP/data-warehouse apps. are different but let’s not go there in this blog post.

However, using a constant value can be useful at some times when we are in a need to use literals in some cases — say you have a table called PURCHASE_ORDER which has a column called STAT_CODE which has only say 3 distinct values in a 10 million row table. Typically, this will be a very bad column for the creation of an index – however the application rarely queries on 90 and 99 and always queries on only 0 value which at any given point of time will have very less number of records (this is the classic density vs selectivity discussion for index creation) so an index would make sense in this case. But this index may not get used under certain scenario even with a value of 0…consider this:

Suppose that the first time that this SQL runs, a value of 90 is passed (say a purge program), then the optimizer would have hard parsed and opted for a different plan (i.e. not use that index and rightfully so since the data-value of 90 qualifies for say 80% of the records)…the second time (normal processing in the application) that this SQL comes in again (this time with a value of 0), it will be soft parsed and since the plan is still in the cache from the previous run of 90, because of the usage of the bind variable, it will use the same plan again that it generated for the value of 90…even though in this case, this is un-optimal to do so (this issue is also termed as “bind variable peeking” in Oracle or “parameter sniffing” in SQL Server)…you can read up more on bind value peeking from the Oracle Guru: Tom Kyte:

http://asktom.oracle.com/pls/ask/f?p=4950:8:948105846467748805::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:7832114438832

In such scenarios, when you know that the data distribution will be heavily skewed and you know that the OLTP application is always interested in only that value that qualifies for lesser number of records and that value is “never” going to change in the query, usage of a constant is better so that it can make use of the data-distribution stats that are available to it (bottom-line: you do not need to bind those values that do not change from execution to execution)…but such requirements are small in an OLTP application. Majority of them fall in the criteria mentioned above (the bind variable usage).