Method and apparatus for projecting the effect of maintaining an auxiliary database structure for use in executing database queries

A computer program product recorded on a computer-readable data storage medium for managing a database in at least one computer system, said at least one computer system including a record of a set of queries which can be executed against said database, said program product comprising:

• a plurality of computer-executable instructions, when executed by at least one computer system, cause the at least one computer system to perform the steps of:
• maintaining a historical record of a set of queries which have been executed against said database, said historical record including, with respect to each of a plurality of executed query instances, at least one of (a) that a respective temporary auxiliary database structure was generated to execute the respective query instance, and (b) that a database optimizer, while determining an optimal query execution strategy for the respective query instance prior to executing the respective query instance, determined that the database optimizer would have used a respective hypothetical auxiliary database structure to optimally execute the respective query instance if the respective hypothetical auxiliary database structure had existed, wherein said record of a set of queries comprises, with respect to each query of a plurality of queries of said set of queries, a respective record of a set of hypothetical auxiliary database structures which would be useful in executing the respective query;
• receiving a specification of a first auxiliary database structure, wherein said first auxiliary database structure is a hypothetical auxiliary database structure which is not defined as part of said database;
• responsive to receiving a specification of said first auxiliary database structure, using said historical record to determine a subset of said set of queries, wherein each respective query of said subset is projected to execute more efficiently using said first auxiliary database structure than the respective query is projected to execute without using said first auxiliary database structure, said subset including each query of said set of queries for which at least one of:
• (a) a said temporary auxiliary database structure matching said first auxiliary database structure was generated to execute a query instance of the respective query, and (b) said database optimizer, while determining an optimal query execution strategy for a query instance of the respective query prior to executing the query instance, determined that the database optimizer would have used a hypothetical auxiliary database structure matching said first auxiliary database structure to optimally execute the query instance of the respective query if the hypothetical auxiliary database structure had existed, wherein said step of determining a subset of said set of queries comprises examining each record of a plurality of said records of a set of hypothetical auxiliary database structures and identifying any queries for which the corresponding record of a set of hypothetical auxiliary database structures contains a hypothetical auxiliary database structure matching said first auxiliary database structure;
• and
• presenting data derived from results of said step of using said historical record to determine a subset of said set of queries to a user.