Delaying evaluation of expensive expressions in a query

• US 20070078812A1
• Filed: 09/30/2005
• Published: 04/05/2007
• 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
• Granted Patent: US 7,877,379 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• 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;

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

determining one or more equivalent execution plans that include an execution plan in which the operation evaluating the expensive expression is delayed relative to 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.

2. The method of claim 1 wherein the step of establishing includes referencing cost information for the expensive expression in an execution plan.

3. The method of claim 1 wherein the step of establishing includes looking up a list of known expensive expressions for a match with the expensive expression.

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

5. 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.

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

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

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

9. The method of claim 8 wherein the query has a constant predicate.

10. 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.

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

12. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 1.

13. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 2.

14. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 3.

15. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 4.

16. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 5.

17. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 6.

18. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 7.

19. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 8.

20. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 9.

21. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 10.

22. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 1.