Method and apparatus for re-evaluating execution strategy for a database query

A computer program product for database query optimization comprising:

• a plurality of computer executable instructions recorded on tangible computer-readable media, wherein said instructions, when executed by at least one computer system, cause the at least one computer system to perform the steps of:
• receiving an invocation of a database query;
• assigning a primary query execution strategy to the database query, wherein said primary execution strategy is a table scan strategy;
• executing said database query according to said primary execution strategy;
• monitoring execution of said primary execution strategy while performing said execution step to detect a condition indicating that said primary execution strategy is sub-optimal, said monitoring execution step including using a non-empty subset of records of a database table already scanned during execution of said database query according to said primary execution strategy to project, at least once during execution of said database query according to said primary execution strategy, whether a number of records selected by a logical condition having an indexed record field within said query will be less than a pre-determined value;
• responsive to detecting a condition indicating that said primary execution strategy is sub-optimal, initiating an alternate execution strategy for executing said database query.

A computer system, comprising:

• at least one processor;
• a data storage for storing a database;
• and
• a database management facility embodied as a plurality of instructions executable on said at least one processor, said database management facility including a query engine which executes logical queries against said database and a query optimizer for generating execution strategies for execution logical queries against said database, wherein for a first said logical query containing at least one imported variable, said query optimizer generates a plurality of different query execution strategies, each said query execution strategy being optimized for a different respective set of values of said at least one imported variable;
• wherein said database management facility automatically selects a primary execution strategy from among said plurality of different query execution strategies generated by said query optimizer for executing an invoked database query, monitors execution of said primary execution strategy to detect a condition indicating that said primary execution strategy is sub-optimal, and initiates an alternate execution strategy from among said plurality of different query execution strategies generated by said query optimizer responsive to detecting a condition indicating that said primary execution strategy is sub-optimal
• wherein said condition indicating that said primary execution strategy is sub-optimal comprises a condition from the set of conditions consisting of:
• (a) a number of records evaluated using said primary execution strategy exceeds a high water mark, said high water mark being a dynamically maintained value indicating the highest number of records evaluated during any of previous successful executions of said primary execution strategy, and
• (b) a projected number of records to be evaluated using said primary execution strategy will be less than a low water mark, said projected number of records being projected during execution of said primary execution strategy using at least some results from execution of said primary execution strategy, said low water mark being a dynamically maintained value indicating the lowest number of records evaluated during any of previous successful executions of said primary execution strategy.