db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Pendleton <bpendleton.de...@gmail.com>
Subject Re: Log indexes used by queries
Date Tue, 26 Mar 2013 01:37:34 GMT
> *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


Mime
View raw message