Patent ID: 7406477
Filing Date: 2008-07-29
Classification: G06F,Y10S

Abstract:
1. In a database system including a database optimizer normally used for generating an access plan for processing a given database query run against the database system, an optimizer-based method for recommending database indexes to be created for maximizing system performance, the method comprising: capturing a workload representative of database queries employed during prior database system use; monitoring the database optimizer as it prepares to optimize each of the queries, by recording all potential database physical indexes which do not currently exist in the database and for which the database optimizer searched during a preoptimization phase that occurs prior to access plan generation; creating an initial set of virtual indexes each simulating presence of a class of potential database physical indexes that were recorded during said monitoring step, wherein each said virtual index comprises an in-memory data structure corresponding to a set of potential database physical indexes; computing cost benefits for different subsets of the set of virtual indexes by invoking the database optimizer again for purposes of providing cost estimates for the workload for each such subset, progressively eliminating a fixed percentage of virtual indexes with the lowest expected improvement in the query's estimated cost from consideration until space that would be occupied by the virtual indexes is less than or equal to a user-specified value; and recommending database physical indexes to be created based on those virtual indexes that have favorable estimated cost benefits for the captured workload and have not been eliminated from consideration.