Monitoring usage of components in a database index
View Patent ↗Database indexes include multiple fields that are used to track how the components of the index are used. An index component monitoring mechanism tracks how the components of the index are used, and updates fields in the index accordingly. Such component-level statistics allow a query optimizer to make more intelligent decisions regarding when to build a new index and when to discard an index, and also provide information regarding which queries are using the index, the maintenance cost of the index, and the selectivity of the index when used to process a query.
1. An apparatus comprising:
at least one processor;
a memory coupled to the at least one processor;
a database index residing in the memory that references a database table, the database index comprising:
a plurality of components that each correspond to a column in the database table;
a plurality of fields that contain information regarding usage of the plurality of components wherein the plurality of fields include:
an indication of how one of the plurality of components has been used;
a correlation of the plurality of components to a corresponding plurality of query identifiers to indicate each query that used a corresponding one of the plurality of components in the database index;
a plurality of last used timestamps correlated to a corresponding plurality of query identifiers to indicate a timestamp for each query that used the database index;
maintenance statistics that indicate which components in the database index were modified when the database index was modified; and
an index component monitoring mechanism that monitors usage of the plurality of components in the database index and writes information regarding the usage to the plurality of fields in the database index, wherein the index component monitoring mechanism includes the option of being turned on and off.