Efficient interaction among cost-based transformations

• US 20070179947A1
• Filed: 03/08/2007
• Published: 08/02/2007
• Est. Priority Date: 06/22/2004
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Ahmed, Rafi, Lee, Allison
• Granted Patent: US 7,702,627 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access

Abstract

During query optimization, when a particular type of transformation is considered, the effect of performing one or more subsequent kinds of transformations is also considered in conjunction with the first. When applying a transformation, which forecloses applying another, both are considered independently.

Claims

1. A method of transforming a certain query, comprising:

making a determination of whether or not to rewrite a certain query using a first transformation, wherein the determination includes;

applying the first transformation to the certain query to generate a first query;

generating a second query based on the first query by applying a second transformation;

determining a query execution cost of the second query; and

making the determination based on the query execution cost the second query.

2. The method of claim 1, wherein the first transformation includes subquery unnesting.

3. The method of claim 2, wherein the second transformation includes join-predicate push down.

4. The method of claim 1, wherein the first transformation includes group-by placement and the second transformation includes join predicate push down.

5. The method of claim 1, wherein the determination is based on the cost of the first query.

6. The method of claim 1, wherein the certain query does not qualify for the second transformation'"'"'s type of transformation.

7. The method of claim 1, wherein the step of generating a second query includes:

applying another transformation to the certain query to generate another query; and

applying the second transformation to the another query to generate the second query.

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

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

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

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

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

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

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

8. A method of transforming a certain query, comprising:

making a determination of whether or not to rewrite a certain query using a first transformation, wherein the determination includes;

applying the first transformation to said certain query to generate a first query;

applying a second transformation to the certain query to generate a second query;

generating a first query execution cost for the first query;

generating a second query execution cost for the second query; and

wherein the determination is based on the first query execution cost and the second query execution cost.

9. The method of claim 8, wherein:

the first query is not qualified for the second transformation'"'"'s type of transformation;

and

the second query is not qualified for first transformation'"'"'s type of transformation.

10. The method of claim 8, wherein:

the first transformation includes a view merging; and

the second transformation includes a join predicate push down.

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

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

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