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

• US 20060074874A1
• Filed: 09/30/2004
• Published: 04/06/2006
• Est. Priority Date: 09/30/2004
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Day, Paul Reuben, Muras, Brian Robert, Mittelstadt, Roger Alan, Jameson, Lee William
• Granted Patent: US 8,046,354 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/24547 Optimisations to support specific applications; Extensibility of optimisers
• CPC Class Codes: G06F 16/24549 Run-time optimisation

Abstract

A query facility for database queries saves and re-uses query execution strategies, and automatically detects that a strategy should be re-optimized as a result of changes to an indexed variable value. Preferably, the number of records evaluated and selected by the index value condition is dynamically monitored during execution, and an alternative query strategy is initiated when this number appears to be out of proportion to expectations. In an exemplary embodiment, an indexed search strategy is changed to a table scan strategy (or vice versa) as a result of a change to the number of records eliminated by the indexed value, although other examples are possible. Preferably, the query engine initiates an alternative search strategy and concurrently continues to execute the original search strategy as separate threads. Search results are obtained from whichever thread completes first.

Claims

1. A method for executing a database query in a computer system, comprising the steps of:

invoking a database query;

automatically assigning a primary query execution strategy to the database query;

executing said database query according to said primary execution strategy;

automatically 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; and

responsive to detecting a condition indicating that said primary execution strategy is sub-optimal, automatically initiating an alternate execution strategy for executing said database query.

2. The method for executing a database query of claim 1, wherein said step of automatically monitoring execution of said primary execution strategy to detect a condition comprises monitoring whether a number of records evaluated using said primary execution strategy exceeds a pre-determined value.

3. The method for executing a database query of claim 2, wherein said pre-determined value is a high water mark indicating the highest number of records evaluated during any of previous successful executions of said primary execution strategy.

4. The method for executing a database query of claim 1, wherein said step of automatically initiating an alternate execution strategy comprises initiating said alternate execution strategy as a separate concurrently executing thread while continuing to execute said database query according to said primary execution strategy.

5. The method for executing a database query of claim 1, wherein said primary execution strategy is an index search strategy.

6. The method for executing a database query of claim 5, wherein said step of automatically monitoring execution of said primary execution strategy comprises monitoring whether a number of records evaluated using said index search strategy exceeds a pre-determined value.

7. The method for executing a database query of claim 5, wherein said alternate execution strategy is a table scan strategy.

8. The method for executing a database query of claim 5, wherein said alternate execution strategy is an index search strategy, said alternate execution strategy using an indexed field other than an indexed field used by said primary search strategy.

9. The method for executing a database query of claim 1, wherein said primary execution strategy is a table scan strategy, and said step of automatically monitoring execution of said primary execution strategy comprises projecting, 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.

10. A computer program product for database query optimization comprising:

a plurality of computer executable instructions recorded on signal-bearing 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;

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; and

responsive to detecting a condition indicating that said primary execution strategy is sub-optimal, initiating an alternate execution strategy for executing said database query.

11. The computer program product of claim 10, wherein said step of monitoring execution of said primary execution strategy to detect a condition comprises monitoring whether a number of records evaluated using said primary execution strategy exceeds a pre-determined value.

12. The computer program product of claim 11, wherein said pre-determined value is a high water mark indicating the highest number of records evaluated during any of previous successful executions of said primary execution strategy.

13. The computer program product of claim 10, wherein said step of initiating an alternate execution strategy comprises initiating said alternate execution strategy as a separate concurrently executing thread while continuing to execute said database query according to said primary execution strategy.

14. The computer program product of claim 10, wherein said primary execution strategy is an index search strategy.

15. The computer program product of claim 14, wherein said step of monitoring execution of said primary execution strategy comprises monitoring whether a number of records evaluated using said index search strategy exceeds a pre-determined value.

16. The computer program product of claim 14, wherein said alternate execution strategy is a table scan strategy.

17. The computer program product of claim 14, wherein said alternate execution strategy is an index search strategy, said alternate execution strategy using an indexed field other than an indexed field used by said primary search strategy.

18. The computer program product of claim 10, wherein said primary execution strategy is a table scan strategy, and said step of monitoring execution of said primary execution strategy comprises projecting, 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.

19. 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 said database management facility automatically assigns a primary execution strategy to 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 responsive to detecting a condition indicating that said primary execution strategy is sub-optimal.

20. The computer system of claim 19, wherein said database management facility monitors execution of said primary execution strategy by monitoring a number of records evaluated using said primary execution strategy and comparing said number of a pre-determined value.