Index advisor

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: https://github.com/lelle1234/Db2Utils/blob/master/IndexAdvisor/index_advisor.py
which demonstrates the technique. It evaluates all possible index combinations and the estimated cost savings for the suggested index combinations