Multi-tier query processing

• US 20050283471A1
• Filed: 06/22/2004
• Published: 12/22/2005
• Est. Priority Date: 06/22/2004
• Status: Abandoned Application

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Ahmed, Rafi
• Application Number:
  US10/874,400
• Publication Number:
  US 20050283471A1
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/90335 Query processing

Abstract

Techniques are provided for processing a query including determining a first cost based on the original query; if the query has a subquery, generating a second query with the subquery unnested; determining a second cost based on the second query; determining whether the second query includes a mergeable view; and if the second query includes a mergeable view, then generating a third query with the view merged; determining a third cost based on the third query; and choosing an output query from among the set of semantically equivalent queries based on costs associated with the semantically equivalent queries, where the set of semantically equivalent queries includes two or more of the original query, the second query, and the third query.

Claims

1. A method of processing a query comprising the machine-implemented steps of:

determining a first cost based on the query;

if the query has a subquery, performing the steps of;

performing a first unnesting operation on the subquery;

generating a second query based on the query and the first unnesting operation;

determining a second cost based on the second query;

determining whether the second query comprises a mergeable view;

if the second query comprises a mergeable view, performing the steps of;

performing a first view merge transformation on the second query;

generating a third query based on the second query and the first view merge transformation;

determining a third cost based on the third query; and

choosing an output query from among a set of semantically equivalent queries based on costs associated with one or more queries from the set of semantically equivalent queries, wherein the set of semantically equivalent queries includes at least two of the query, the second query, and the third query.

2. The method of claim 1, further comprising the steps of:

if the query has a second subquery, performing the steps of;

generating a fourth query based on the query and performing no unnesting operations on the subquery and the second subquery;

determining a fourth cost based on the fourth query;

performing a second unnesting operation on the second subquery;

generating a fifth query based on the fourth query and the second unnesting operation;

determining a fifth cost based on the fifth query;

determining whether the fifth query comprises a mergeable view;

if the fifth query comprises a mergeable view, performing the steps of;

performing a second view merge transformation on the fifth query;

generating a sixth query based on the fifth query and the second view merge transformation;

determining a sixth cost based on the sixth query; and

wherein the set of semantically equivalent queries also includes the fourth query, the fifth query, and the sixth query.

3. The method of claim 2, further comprising the steps of:

if the query has the second subquery, performing the steps of;

generating a seventh query based on the sixth query and the first unnesting operation;

determining a seventh cost based on the seventh query;

determining whether the seventh query comprises a mergeable view;

if the seventh query comprises a mergeable view, performing the steps of;

performing a third view merge transformation on the seventh query;

generating an eighth query based on the seventh query and the third view merge transformation;

determining an eighth cost based on the eighth query; and

the set of semantically equivalent queries also includes the seventh query, and the eighth query.

4. The method of claim 1, wherein the second query is a Structured Query Language (SQL) query, and wherein the step of determining whether the second query comprises a mergeable view comprises determining whether the second query includes an inline view that contains a SQL GROUP BY clause.

5. The method of claim 1, wherein the second query is a SQL query, and wherein the step of determining whether the second query comprises a mergeable view comprises determining whether the second query includes an inline view that contains a DISTINCT key word.

6. The method of claim 1, wherein the second query is a SQL query, and wherein the step of determining whether the second query comprises a mergeable view comprises determining whether the second query includes an inline view that contains a SQL MAX function.

7. The method of claim 1, wherein the second query is a SQL query, and wherein the step of determining whether the second query comprises a mergeable view comprises determining whether the second query includes an inline view that contains a SQL MIN function.

8. The method of claim 1, wherein the second query is a SQL query, and wherein the step of determining whether the second query comprises a mergeable view comprises determining whether the second query includes an inline view that contains a SQL SUM function.

9. The method of claim 1, wherein the step of determining whether the second query comprises a mergeable view comprises determining whether the second query includes an inline view that contains an aggregation function.

10. The method of claim 1, further comprising the steps of:

receiving a request from a sender to execute the query;

if the query has a subquery, executing the output query; and

returning results of the executing step to the sender.

11. The method of claim 1, wherein the steps of the method are performed multiple times and the set of semantically equivalent queries comprises all semantically equivalent queries that can be determined for the query by a query-processing unit.

12. The method of claim 1, wherein the steps of the method are performed one or more times for each query block in the query;

and set of semantically equivalent queries comprises a particular query that contains a lowest-cost alternative form for each query block in the query; and

wherein choosing the output query comprises choosing the particular query.

13. The method of claim 1, wherein the subquery is one of multiple subqueries in the query, and wherein costs are determined for multiple semantically equivalent queries, wherein each semantically equivalent query is generated based on a different combination of original subqueries, unnesting operations, and view merge transformations than each other semantically equivalent query, and wherein the set of semantically equivalent queries includes the multiple semantically equivalent queries.

14. A machine-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.

15. A machine-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.

16. A machine-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.

17. A machine-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.

18. A machine-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.

19. A machine-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.

20. A machine-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.

21. A machine-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.

22. A machine-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.

23. A machine-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.

24. A machine-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 11.

25. A machine-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 12.

26. A machine-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 13.