0

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?

  • 1
    I found this limk that might be useful the only downside it does not work with between,>= and <= predicates – Kumar Nov 29 '19 at 0:01
  • What Db2 version and platform are we talking about? This won't be possible in Db2 for LUW, and I fail to see a scenario where "it would be convenient" (but that's just me). – mustaccio Nov 29 '19 at 1:37
  • @mustaccio The version is 11.1. 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. – Daniel Díaz Carrete Nov 29 '19 at 8:38
2

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.

|improve this answer|||||
1

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.

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.perf.doc/doc/c0070117.html

|improve this answer|||||
  • Thanks. Looking at the documentation, I see guidelines like LPREFETCH that force the use of a given index, but none like Oracle's NO_INDEX that disallow taking an index into consideration. – Daniel Díaz Carrete Nov 29 '19 at 9:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.