Portable and Iterative Re-Usable Suboptimization of Database Queries

• US 20080256024A1
• Filed: 04/13/2007
• Published: 10/16/2008
• Est. Priority Date: 04/13/2007
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Hu, Wei, Kethireddy, Shantan, Thiemann, Ulrich, Passe, Andrew Peter, Downer, Robert Victor
• Granted Patent: US 8,126,873 B2
• Time in Patent Office: Days
• US Class Current: 707/2
• 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, 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;

evaluating the first subplan for resource cost using the retrieved optimization data;

evaluating the second subplan for resource cost; and

evaluating 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 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 wherein the query plan comprises an index plan.

11. The method of claim 1 wherein the query plan comprises an index anding plan.

12. The method of claim 1 further comprising:

in response to a change in data that is part of the database query, evaluating the effect of the change on the first and second subplans; and

reoptimizing the first and second subplans if affected by the change.

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

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

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

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

17. 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, 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.

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

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

20. The apparatus of claim 17 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.

21. The apparatus of claim 17 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.

22. The apparatus of claim 17 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.

23. The apparatus of claim 17 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.

24. The apparatus of claim 23 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.

25. A program product, comprising:

a computer readable medium; and

a program code 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, 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.