Hi, On 26.03.2013 02:45, mvarvil wrote: > What classes and methods could you point me to such that I could log the > index ultimately used by the optimizer? I don't know that there is any easy way to do this except siphoning off information from the logged query plans. In general, gathering usage information over time to tune the database is an interesting topic, which we haven't addressed much. One could imagine updating a data dictionary counter each time an index was used (at compile and/or query time). That would mean altering the data dictionary formats with (a) new column(s), not very hard; maybe total usage count, last time used in a query and possibly other statistics. I'd start looking by how the current query plan is produced. Other ideas?
Thanks, Dag > 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. Comparing the list against > ALL the indexes created in the system should provide a starting point for > identifying extraneous indexes. > > Alternatively, perhaps one might recommend enabling a logger class that > outputs this information already. > > Enabling logQueryPlan is my least favorite idea, because of its verbosity. > I'm open to other ideas too. > > Thanks. > Mark > > > > -- > View this message in context: > http://apache-database.10148.n7.nabble.com/Logging-indexes-used-by-queries-tp128355.html > Sent from the Apache Derby Developers mailing list archive at Nabble.com.
