Index advisor

👍 Gathering votes
Lennart Jonsson

Concider it a nice to to have feauture

Not sure about other vendors, but for Db2 there exists ADVISE tables in addition to EXPLAIN tables. One can for example create "virtual" indexes and have the optimizer compile a new plan and see what the estimated improvement would be. It would be a neat feature to have down at the statement level, say suggest indexes for this statement. In short what one does is:

set current explain mode recomend indexes
exec stmt

and to turn indexes on/off
update advise_index set use_index = $x where name = ? , $x={'Y', 'N'}
set current explain mode evaluate indexes
exec stmt

I've got some sample code which is free to use at:
which demonstrates the technique. It evaluates all possible index combinations and the estimated cost savings for the suggested index combinations

9 months ago

Mick Mcguinness

Thanks for the suggestion Lennart and the link to your script. Recommendations including index recommendations are something we are looking to do more of in DBmarlin next year.

0    9 months ago    Reply

Lennart Jonsson

Great. The script is a bit messy, so if something is unclear feel free to drop me a note and I can - perhaps - elaborate

0    9 months ago    Reply

One vote