Kbase P122692: Why would a query use different indexes based on whether it has hard coded values or runtime paramet
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
08/03/2007 |
|
Status: Unverified
GOAL:
Why would a query use different indexes based on whether it has hard coded values or runtime parameters?
FIX:
The SQL-92 query optimizer chooses the best data access path it can, based on the information it has. When the optimizer runs, it does not know what the values of parameters will be. These values are only known when the query begins execution.
In the case of the query with no parameters, all the values for possible index key use are in the query, and so the optimizer can determine that this index will give good results. In the case of the query with parameters, the optimizer can not always determine what columns can be used as an index key component and subsequently may end up choosing an index other than the one it chose to resolve the query with the hard coded values.