System and method for processing queries having an inner query block containing a grouping operator

• US 6,370,524 B1
• Filed: 04/02/1999
• Issued: 04/09/2002
• Est. Priority Date: 04/02/1999
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle Corporation
• Inventors: Witkowski, Andrew
• Primary Examiner(s): Alam, Hosain T.
• Assistant Examiner(s): Ly, Anh
• Application Number:
  US09/283,755
• Time in Patent Office: 1,103 Days
• Field of Search: 907/1-10
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24535 of sub-queries or views
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation

Abstract

Queries having a outer query block enclosing an inner query block, such as a reference to a view or a subquery, with a grouping operator, such as GROUP or DISTINCT, are transformed by merging the inner query block into the outer query block. The FROM clause of the transformed query includes the tables referenced in the FROM clauses of the outer query block and the inner query block. A new GROUP BY clause is created for the outer query block that contains the rowids of tables referenced in the FROM list of the original query and the expressions in the GROUP BY clause of the inner query block. The outer query block can be split if the outer query block also contains a GROUP BY or HAVING clause.

Claims

1. A method of processing a query in a database system, comprising the computer-implemented steps of:

receiving an original query that contains an inner query block with a grouping operator, said inner query block being embedded in an outer query block;

transforming the original query into a transformed query by;

removing the inner query block from the original query, and merging portions of the inner query block into the outer query block; and

processing the transformed query.

2. The method of claim 1, wherein the transformed query is semantically equivalent to the original query.

3. The method of claim 1, wherein the step of merging includes inserting table references from an inner query block FROM clause into an outer query block FROM clause.

4. The method of claim 3, wherein the inner query block includes a reference to a view.

5. The method of claim 4, wherein the grouping operator is an inner block GROUP BY clause, said inner block GROUP BY clause including expressions.

6. The method of claim 5, wherein the step of merging includes the step of inserting an outer block GROUP BY clause into the outer query block, said outer block GROUP BY clause based on the expressions of the inner block GROUP BY clause.

7. The method of claim 6, wherein the outer block GROUP BY further includes rowids of tables referenced in a FROM list of the outer query block.

8. The method of claim 6, wherein the step of transforming further includes the steps of:

inserting a HAVING clause into the outer query block, and

moving a predicate of a WHERE clause of the outer query block into said HAVING clause, said predicate referencing a column in the view that is aggregated.

9. The method of claim 5, wherein the step of transforming includes the steps of:

splitting the outer query block into a first outer query block and a second outer query block, wherein the first outer query block encloses said second outer query block and includes a first outer query block GROUP BY or HAVING clause of the outer query block; and

merging the inner query block into the second outer query block.

10. The method of claim 4, wherein the grouping operator includes an inner block DISTINCT qualifier for the view.

11. The method of claim 10, wherein the step of transforming includes the steps of:

splitting the outer query block into a first outer query block and a second outer query block, wherein;

the first outer query block encloses said second outer query block,

the second outer query block includes a DISTINCT qualifier and contains a WHERE clause of the outer query block and;

migrating the DISTINCT qualifier from the inner query block to the second outer query block; and

merging the inner query block into the second outer query block.

12. The method of claim 10, wherein the step of transforming includes converting the inner query block into an equivalent inner query block with a GROUP BY clause.

13. The method of claim 3, wherein the inner query block includes a subquery.

14. The method of claim 13, wherein the subquery is a non-correlated subquery.

15. The method of claim 14, wherein the step of transforming includes the step of converting the outer query block into an equivalent outer query block referencing a view based on the non-correlated subquery.

16. A computer-readable medium bearing instructions for processing a query in a database system, said instructions, when executed, arranged for causing one or more processors to perform the steps of:

receiving an original query that contains an inner query block with a grouping operator, said inner query block being embedded in an outer query block;

transforming the original query into a transformed query by;

removing the inner query block from the original query, and merging portions of the inner query block into the outer query block; and

processing the transformed query.

17. The computer-readable medium of claim 16, wherein the transformed query is semantically equivalent to the original query.

18. The computer-readable medium of claim 16, wherein the step of merging includes inserting table references from an inner query block FROM clause into an outer query block FROM clause.

19. The computer-readable medium of claim 18, wherein the inner query block includes a reference to a view.

20. The computer-readable medium of claim 19, wherein the grouping operator is an inner block GROUP BY clause, said inner block GROUP BY clause including expressions.

21. The computer-readable medium of claim 20, wherein the step of merging includes the step of inserting an outer block GROUP BY clause into the outer query block, said outer block GROUP BY clause based on the expressions of the inner block GROUP BY clause.

22. The computer-readable medium of claim 21, wherein the outer block GROUP BY further includes rowids of tables referenced in a FROM list of the outer query block.

23. The computer-readable medium of claim 21, wherein the step of transforming further includes the steps of:

inserting a HAVING clause into the outer query block, and

moving a predicate of a WHERE clause of the outer query block into said HAVING clause, said predicate referencing a column in the view that is aggregated.

24. The computer-readable medium of claim 20, wherein the step of transforming includes the steps of:

splitting the outer query block into a first outer query block and a second outer query block, wherein the first outer query block encloses said second outer query block and includes a first outer query block GROUP BY or HAVING clause of the outer query block; and

merging the inner query block into the second outer query block.

25. The computer-readable medium of claim 19, wherein the grouping operator includes an inner block DISTINCT qualifier for the view.

26. The computer-readable medium of claim 25, wherein the step of transforming includes the steps of:

splitting the outer query block into a first outer query block and a second outer query block, wherein;

the first outer query block encloses said second outer query block, the second outer query block includes a DISTINCT qualifier and contains a WHERE clause of the outer query block and;

migrating the DISTINCT qualifier from the inner query block to the second outer query block; and

merging the inner query block into the second outer query block.

27. The computer-readable medium of claim 25, wherein the step of transforming includes converting the inner query block into an equivalent inner query block with a GROUP BY clause.

28. The computer-readable medium of claim 18, wherein the inner query block includes a subquery.

29. The computer-readable medium of claim 28, wherein the subquery is a non-correlated subquery.

30. The computer-readable medium of claim 29, wherein the step of transforming includes the step of converting the outer query block into an equivalent outer query block referencing a view based on the non-correlated subquery.