Delaying evaluation of expensive expressions in a query

• US 7,877,379 B2
• Filed: 09/30/2005
• Issued: 01/25/2011
• Est. Priority Date: 09/30/2005
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Das, Dinesh, Zait, Mohamed, Waingold, Allison, Ahmed, Rafi, Ghosh, Bhaskar
• Primary Examiner(s): Le; Miranda
• Application Number:
  US11/242,361
• Publication Number:
  US 20070078812A1
• Time in Patent Office: 1,943 Days
• Field of Search: 707/1, 707/2, 707/3, 707/4, 707/100, 707/718, 707/719, 707/999.002
• US Class Current: 707/718
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing

Abstract

Techniques are provided for delaying evaluation of expensive expressions in a query. Expensive expressions in the query are established by cost information or by looking up a list of known expensive expressions for a match. After an execution plan is determined by using the early evaluation technique, one or more equivalent execution plans is established. The one or more equivalent execution plans may include both a type of execution plans that delay evaluation of expensive expressions and a type of execution plans that do not. In addition, the one or more equivalent execution plans may include both parallelized and non-parallelized alternatives to the execution plan identified by the early evaluation technique. Finally, based on a set of criteria, which may include comparing cost information among all the equivalent execution plans generated thus far, the best execution plan is chosen for the query.

Claims

1. A computer-implemented method of generating an execution plan for a query, comprising:

establishing an expensive expression in the query;

wherein establishing the expensive expression includes one or more of;

referencing cost information for the expensive expression in an execution plan; and

looking up a list of known expensive expressions for a match with the expensive expression;

generating a first execution plan for the query in which an operation evaluating the expensive expression occurs no later than a non-empty set of operations that do not evaluate the expensive expression;

generating one or more equivalent execution plans for the query, wherein generating the one or more equivalent execution plans includes generating, in response to said establishing the expensive expression in the query, an execution plan in which the operation evaluating the expensive expression occurs after the non-empty set of operations not evaluating the expensive expression; and

choosing, based on a set of criteria, a particular execution plan from the first execution plan and the one or more equivalent execution plans;

wherein the method is performed by one or more computing devices.

2. The method of claim 1 wherein the list of known expensive expressions is configurable.

3. The method of claim 1 wherein the non-empty set of operations not evaluating the expensive expression includes an operation relating to a blocking construct.

4. The method of claim 3 wherein the blocking construct is an ORDER BY, WINDOW SORT, GROUP BY SORT, or DISTINCT SORT construct.

5. The method of claim 1, wherein the one or more equivalent execution plans includes a non-parallelized execution plan for the query.

6. The method of claim 1 wherein the one or more equivalent execution plans includes a parallelized execution plan for the query.

7. The method of claim 6 wherein the query has a constant predicate.

8. The method of claim 1 wherein the step of choosing includes referencing cost information for each of the first execution plan and the one or more equivalent execution plans.

9. The method of claim 8 wherein the cost information includes usage information of CPU, memory, disk access, or network communication.

10. A machine-readable non-transitory medium storing one or more sequences of instructions for generating an execution plan for a query which instructions, when executed by one or more processors, cause performance of steps comprising:

establishing an expensive expression in the query;

wherein establishing the expensive expression includes one or more of;

referencing cost information for the expensive expression in an execution plan; and

looking up a list of known expensive expressions for a match with the expensive expression;

generating a first execution plan for the query in which an operation evaluating the expensive expression occurs no later than a non-empty set of operations that do not evaluate the expensive expression;

generating one or more equivalent execution plans for the query, wherein generating the one or more equivalent execution plans includes generating, in response to said establishing the expensive expression in the query, an execution plan in which the operation evaluating the expensive expression occurs after the non- empty set of operations not evaluating the expensive expression; and

choosing, based on a set of criteria, a particular execution plan from the first execution plan and the one or more equivalent execution plans.

11. The machine-readable non-transitory volatile or non-volatile medium of claim 10, wherein the list of known expensive expressions is configurable.

12. The machine-readable non-transitory medium of claim 10, wherein the non-empty set of operations not evaluating the expensive expression includes an operation relating to a blocking construct.

13. The machine-readable non-transitory medium of claim 12, wherein the blocking construct is an ORDER BY, WINDOW SORT, GROUP BY SORT, or DISTINCT SORT construct.

14. The machine-readable non-transitory medium of claim 10, wherein the one or more equivalent execution plans includes a non-parallelized execution plan for the query.

15. The machine-readable non-transitory medium of claim 10, wherein the one or more equivalent execution plans includes a parallelized execution plan for the query.

16. The machine-readable non-transitory medium of claim 15, wherein the query has a constant predicate.

17. The machine-readable non-transitory medium of claim 10, wherein the instructions that cause choosing the particular execution plan include instructions which, when executed by the one or more processors, cause the one or more processors to perform referencing cost information for each of the first execution plan and the one or more equivalent execution plans.

18. The machine-readable non-transitory medium of claim 17, wherein the cost information includes usage information of CPU, memory, disk access, or network communication.