Database query optimization apparatus and method that represents queries as graphs

• US 20030120682A1
• Filed: 12/11/2001
• Published: 06/26/2003
• Est. Priority Date: 12/11/2001
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Bestgen, Robert Joseph, Downer, Robert Victor, Dietel, John David, Boger, Curtis
• Granted Patent: US 6,915,290 B2
• Time in Patent Office: Days
• US Class Current: 707/104.1
• CPC Class Codes: G06F 16/24526 Internal representations for queries
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing

Abstract

A database query optimizer constructs a graph comprising nodes, relations, and expressions. The query optimizer then constructs execution plans for sub-parts of the graph. The combination of execution plans make up the overall execution plan for the query. The execution plan information is appended to the graph itself, allowing changing an execution plan in one portion of the graph without necessarily changing execution plans in other portions of the graph. By representing a query using the graph of the preferred embodiments that includes execution plan information, the query optimizer is able to evaluate the execution plans of different options quickly and efficiently, thereby enhancing the performance of the query optimizer.

Claims

1. An apparatus comprising:

at least one processor;

a memory coupled to the at least one processor; and

an optimizer residing in the memory and executed by the at least one processor, the optimizer analyzing an expression and generating from the expression a graph that includes at least one node, the optimizer generating from the graph an execution plan for the expression, the execution plan comprising a plurality of execution plans that correspond to different portions of the graph.

2. The apparatus of claim 1 wherein the plurality of execution plans are appended to corresponding nodes in the graph.

3. The apparatus of claim 1 wherein the optimizer generates a new execution plan for the query by changing at least one of the plurality of execution plans, and by using an existing execution plan for each portion of the graph that is unaffected by the change.

4. The apparatus of claim 1 wherein the graph further comprises a plurality of relations and a plurality of expressions.

5. An apparatus comprising:

at least one processor;

a memory coupled to the at least one processor;

a database residing in the memory;

a database query optimizer residing in the memory and executed by the at least one processor, the database query optimizer processing a query to the database, the database query optimizer comprising;

a graph builder that generates from the query a graph that includes at least one node; and

an execution plan generator that generates from the graph an execution plan for the query, the execution plan comprising a plurality of execution plans that correspond to different portions of the graph.

6. The apparatus of claim 5 wherein the execution plan generator appends the plurality of execution plans to corresponding nodes in the graph.

7. The apparatus of claim 5 wherein the execution plan generator generates a new execution plan for the query by changing at least one of the plurality of execution plans, and by using an existing execution plan for each portion of the graph that is unaffected by the change.

8. The apparatus of claim 5 wherein the graph further comprises a plurality of relations and a plurality of expressions in the query.

9. The apparatus of claim 5 wherein the optimizer compares a plurality of execution plans that each functionally represent the query to estimate which of the plurality of execution plans will be executed in the least amount of time.

10. A method for evaluating an expression comprising the steps of:

reading the expression;

generating from the expression a graph that includes at least one node;

generating from the graph an execution plan for the expression, the execution plan comprising a plurality of execution plans that correspond to different portions of the graph.

11. The method of claim 10 further comprising the step of appending the plurality of execution plans to corresponding nodes in the graph.

12. The method of claim 10 further comprising the step of generating a new execution plan for the query by performing the steps of:

changing at least one of the plurality of execution plans; and

using an existing execution plan for each portion of the graph that is unaffected by the change.

13. The method of claim 10 further comprising the step of comparing a plurality of execution plans that each functionally represent the query to determine which of the plurality of execution plans will likely be executed in the least amount of time.

14. A program product comprising:

(A) an optimizer that analyzes an expression and generates from the expression a graph that includes at least one node, the optimizer generating from the graph an execution plan for the expression, the execution plan comprising a plurality of execution plans that correspond to different portions of the graph; and

(B) computer-readable signal bearing media bearing the optimizer.

15. The program product of claim 14 wherein the computer-readable signal bearing media comprises recordable media.

16. The program product of claim 14 wherein the computer-readable signal bearing media comprises transmission media.

17. The program product of claim 14 wherein the optimizer appends the plurality of execution plans to corresponding nodes in the graph.

18. The program product of claim 14 wherein the optimizer generates a new execution plan for the query by changing at least one of the plurality of execution plans, and by using an existing execution plan for each portion of the graph that is unaffected by the change.

19. The program product of claim 14 wherein the graph further comprises a plurality of relations and a plurality of expressions.

20. The program product of claim 14 wherein the optimizer compares a plurality of execution plans that each functionally represent the expression to estimate which of the plurality of execution plans will be executed in the least amount of time.