I'm trying to evaluate different approaches to analyzing the benefit of changing the set of indexes that are available. If I pick some representative queries from my workload, I can run EXPLAIN and check if the plan of each changes when I add/drop an index. However, this may be too tedious if I have, say 100 queries. I was wondering what available alternatives (tools, scripts, etc.) exist and what are their pros/cons.
Db2 – In DB2, what are the alternative ways of evaluating an index’s (or several indexes) benefit
database-designdb2index-tuningoptimization
Related Question
- DB2 – What is the difference between online backup and AUTO_DB_BACKUP
- DB2 Indexes are taking very large size on disk space
- Db2 – How to know what are the necessary log files for a backup in DB2
- Db2 – what is the formal statement terminator with db2 sql
- Mysql – What are the right ways to analyse and tune up innodb_thread_concurrency in thesql 5.7
Best Answer
The best way to do this for a DB2 database running on Linux, UNIX or Windows is use the DB2 Design Advisor.
This tool (included with DB2) allows you to specify a workload (which could include hundreds of queries), and it will analyze the workload and the existing database to recommend indexes and/or other physical design changes to optimize the workload.