Portable and iterative re-usable suboptimization of database queries

• US 8,126,873 B2
• Filed: 04/13/2007
• Issued: 02/28/2012
• Est. Priority Date: 04/13/2007
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Downer, Robert Victor, Hu, Wei, Kethireddy, Shantan, Passe, Andrew Peter, Thiemann, Ulrich
• Primary Examiner(s): Trujillo, James
• Assistant Examiner(s): Singh, Amresh
• Application Number:
  US11/735,095
• Publication Number:
  US 20080256024A1
• Time in Patent Office: 1,782 Days
• Field of Search: 707/2, 707/720
• US Class Current: 707/720
• CPC Class Codes: G06F 16/24542 Plan optimisation

Abstract

A method, apparatus and program product to optimize a database query. A query plan is generated for the database query where the query plan includes first and second subplans. Cached optimization data associated with the first subplan and generated during evaluation of another query plan for a previous query that uses the first subplan is retrieved. The first subplan is evaluated for resource cost using the retrieved optimization data. The second subplan is also evaluated for resource cost. The query plan is then evaluated for resource cost based upon the evaluations of resource cost for the first and second subplans. A query plan is selected based on evaluations of resource cost and the plan is executed to generate a result set.

Claims

1. A method for optimizing a database query on a computer, the method comprising:

generating a query plan for the database query, wherein the query plan includes first and second subplans;

retrieving cached optimization data associated with the first subplan and generated during evaluation of another query plan for a previous query that uses the first subplan, wherein the cached optimization data does not include cached query results, and wherein the cached optimization data is generated during evaluation of another query plan for a previous database query for which only partial optimization is completed and a portion of statistics collection for the other query plan is deferred;

evaluating by the hardware-implemented processor on the computer the first subplan for resource cost using the retrieved optimization data;

evaluating by the hardware-implemented processor on the computer the second subplan for resource cost; and

evaluating by the hardware-implemented processor on the computer the query plan for resource cost based upon the evaluations of resource cost for the first and second subplans.

2. The method of claim 1 further comprising caching new optimization data associated with the first and second subplans.

3. The method of claim 1 wherein evaluating the second subplan does not rely on cached optimization data.

4. The method of claim 1 wherein evaluating the first and second subplans for resource cost comprises:

collecting statistics on the first and second subplans; and

estimating resource costs based on the collected statistics.

5. The method of claim 1 wherein evaluating the query plan for resource costs comprises:

comparing estimated resource costs between subplans;

selecting or discarding subplans based on the resource costs; and

estimating resource costs for the query plan based on the selected subplans.

6. The method of claim 1 further comprising:

selecting the query plan based on evaluations of resource cost; and

executing the query plan on the hardware-implemented processor on the computer to generate a result set.

7. The method of claim 1 wherein the optimization data comprises a partially optimized representation of the first subplan.

8. The method of claim 1 wherein the optimization data comprises a completely optimized representation of the first subplan.

9. The method of claim 1 wherein the optimization data comprises statistics collected for the first subplan.

10. The method of claim 1 further comprising:

in response to a change in data that is part of the database query, evaluating by the hardware-implemented processor on the computer the effect of the change on the first and second subplans; and

reoptimizing the first and second subplans by the hardware-implemented processor on the computer if affected by the change.

11. The method of claim 10 wherein the change in data is a change in data causing an index that has become unavailable.

12. The method of claim 10 wherein the change in data is a change in data causing an increase in table size.

13. The method of claim 12 wherein the increase in table size is about 10 percent or more.

14. The method of claim 10 wherein the change in data includes using or no longer using symmetric multiprocessing.

15. The method of claim 1, further comprising:

during evaluation of the other query plan, partially optimizing the other query plan and deferring completion of optimizing the other query plan, wherein partially optimizing the other query plan includes partially optimizing the first subplan such that the cached optimization data includes at least one of a partially optimized representation of the first subplan and progress data generated during partial optimization of the first subplan; and

completing optimization of the first subplan during evaluation of the query plan.

16. An apparatus comprising:

a processor; and

program code configured to be executed by the processor to optimize a database query, the program code configured to generate a query plan for the database query wherein the query plan includes first and second subplans, retrieve cached optimization data associated with the first subplan and generated during evaluation of another query plan for a previous query that uses the first subplan wherein the cached optimization data does not include cached query results, evaluate the first subplan for resource cost using the retrieved optimization data, evaluate the second subplan for resource cost, and evaluate the query plan for resource cost based upon the evaluations of resource cost for the first and second subplans, wherein the cached optimization data is generated during evaluation of another query plan for a previous database query for which only partial optimization is completed and a portion of statistics collection for the other query plan is deferred.

17. The apparatus of claim 16 wherein the program code is further configured to cache new optimization data associated with the first and second subplans.

18. The apparatus of claim 16 wherein the program code is configured to evaluate the second subplan without relying on cached optimization data.

19. The apparatus of claim 16 wherein the program code is configured to evaluate the first and second subplans for resource cost by collecting statistics on the first and second subplans and estimating resource costs based on the collected statistics.

20. The apparatus of claim 16 wherein the program code is configured to evaluate the query plan for resource costs by comparing estimated resource costs between subplans, selecting or discarding subplans based on the resource costs, and estimating resource costs for the query plan based on the selected subplans.

21. The apparatus of claim 16 wherein the program code is further is configured to select the query plan based on evaluations of resource cost and execute the query plan to generate a result set.

22. The apparatus of claim 16 wherein the program code is further configured to, in response to a change in data that is part of the database query, evaluate the effect of the change on the first and second subplans and reoptimize the first and second subplans if affected by the change.

23. The apparatus of claim 22 wherein the change in data is selected from the group consisting of a change in data causing an index that to become unavailable, a change in data causing an increase in table size of about 10 percent or more, and a decision to use or no longer use symmetric multiprocessing.

24. The apparatus of claim 16, wherein optimization data is selected from a group consisting of a partially optimized representation of the first subplan, a completely optimized representation of the first subplan, statistics collected for the first subplan, and combinations thereof.

25. A program product, comprising:

a computer recordable-type medium; and

a program code resident on the recordable-type medium and configured to optimize a database query, the program code further configured to generate a query plan for the database query wherein the query plan includes first and second subplans, retrieve cached optimization data associated with the first subplan and generated during evaluation of another query plan for a previous query that uses the first subplan wherein the cached optimization data does not include cached query results, evaluate the first subplan for resource cost using the retrieved optimization data, evaluate the second subplan for resource cost, and evaluate the query plan for resource cost based upon the evaluations of resource cost for the first and second subplans, wherein the cached optimization data is generated during evaluation of another query plan for a previous database query for which only partial optimization is completed and a portion of statistics collection for the other query plan is deferred.