Question :
I’m trying to evaluate the impact of a new index on various queries that run against a DB2 11.1 database.
It would be convenient to be able to force the optimizer to ignore the new index on a query-by-query basis. Oracle would let me do this through optimizer hints, for example NO_INDEX(EMPLOYEES EMPLOYEE_PK).
The convenience comes from not having to delete and re-create alternative indexes when comparing their effects on a query, from not requiring “stateful” configuration changes that you must remember to revert, and from avoiding interference with other users’ queries.
Is there an analogous mechanism in DB2 for that?
Answer :
There is no equivalent Access Request with Db2 Optimization Guidelines unfortunately.
You may specify either desired index to use or another access method (TBSCAN, for example) for your table. But not “not to use some particular index” guideline.
Have you looked at Db2 optimizer guidelines?
Optimization guidelines are rules that you create to control the decisions made during the optimization stages of query compilation. These guidelines are recorded and used by an optimization profile. Guidelines can be written at the global level or statement level. Many query compiler decisions can be controlled through the creation of optimization guidelines.