A Method and System to Enhance Query Performance by Autonomically Creating and Deleting Database Indices at Runtime.
Original Publication Date: 2003-Sep-24
Included in the Prior Art Database: 2003-Sep-24
The following is disclosed: a method and a system is described that can autonomically create and delete indices during runtime taking the query load into account. Whenever a query is issued to the database the query is parsed by the database optimizer module to determine the best access path to execute the query. In this process the query criterion or say query predicates are extracted and populated into a database system table and a reference count is maintained. There is an index scheduler that periodically scans through this table and recognizes the table column predicates in the SQL queries that are frequently used and do not participate in any index, are created as indices. Also if there are any columns that are not referenced frequently and have an index are deleted and reclaimed for space. The scheduler invocation and the determination of reference count etc. are controlled by the parameters in the database configuration file which control the overall database system behavior.