Database system with methodology for automated determination and selection of optimal indexes

A system that recommends database indexes to be created for optimizing system performance, the system comprising:

• a computer having at least one processor;
• a database system that executes database queries, said database system including a database optimizer normally used for generating an access plan for processing each given database query run against the database system;
• and
• an optimizer-based index consultant for capturing a workload representative of database queries executed during prior system use, which monitors the database optimizer as it prepares to optimize each of the queries by recording all potential database physical indexes that 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, and which creates an initial set of virtual indexes each simulating presence of a class of potential database physical indexes that were recorded during monitoring, wherein each said virtual index comprises an in-memory data structure corresponding to a set of potential database physical indexes, wherein cost benefits for different subsets of the set of virtual indexes are computed 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 wherein database physical indexes are recommended 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.