*My objective* is to identify useless and extraneous indexes in the database. I would like to capture a list of indexes used by the database while it is in use by an application over a period of time.
This sounds like a great tool! It would be neat if you could develop this in such a way that you could contribute it back to the community... > Enabling logQueryPlan is my least favorite idea, because of its verbosity. I understand :) But is it the correct basic data for your needs? You can get access to roughly the same collection of data, but in a more structured format that's easier to write programs to process, by using XPLAIN style: http://db.apache.org/derby/docs/10.9/tuning/ctun_xplain_style.html I suggest approaching your overall project incrementally: turn on the existing data capturing tools (e.g., XPLAIN data capture, or query plan logging, or RUNTIMESTATISTICS, etc.). Then, try to process that data to report on index usage, as you sketched out in your original message. Then, as you find places where this is challenging (for example, it seems you're not capturing quite enough information), work with the community to enhance the data capture tools to meet your needs. Hope this helps, and let us know how your project goes! thanks, bryan
