Rewriting a query in terms of a summary based on aggregate computability and canonical format, and when a dimension table is on the child side of an outer join

• US 5,991,754 A
• Filed: 12/28/1998
• Issued: 11/23/1999
• Est. Priority Date: 12/28/1998
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle Corporation
• Inventors: Raitto, John, Finnerty, James, Ziauddin, Mohamed
• Primary Examiner(s): Kulik, Paul V.
• Application Number:
  US09/222,249
• Time in Patent Office: 330 Days
• Field of Search: 707/4, 707/3, 707/5, 707/2
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: G06F 16/24556 Aggregation; Duplicate elimination
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation

Abstract

A method and system are provided for processing queries, where the queries do not reference a particular materialized view. Specifically, techniques are provided for handling a query that specifies a first set of one or more aggregate functions, where the particular materialized view reflects a second set of one or more aggregate functions. Whether the query can be rewritten is determined based on the aggregate functions in the first and second sets, and the corresponding arguments. Techniques are also provided for processing a query that (1) does not reference a particular materialized view, (2) specifies a first set of one or more aggregate functions, where the particular materialized view reflects a second set of one or more aggregate functions. A technique is also provided for rewriting queries that specify an outer join that has a dimension table on the child-side of the outer join and a fact table on the parent-side of the outer join. The query is rewritten to produce a rewritten query by replacing references to the fact table in the query with references to a materialized view. The rewritten query specifies an outer join that has the dimension table on the child side and the materialized view on the parent side.

Claims

1. A method for processing queries, the method comprising the steps of:

receiving a query that does not reference a particular materialized view;

said query specifying a first set of one or more aggregate functions;

said particular materialized view reflecting a second set of one or more aggregate functions;

determining whether the particular materialized view satisfies each condition in a set of conditions, the set of conditions at least including;

that each aggregate function in said first set of aggregate functions be computable from one or more corresponding aggregate functions in said second set of aggregate functions; and

that the argument to each aggregate function in said first set of aggregate functions be equivalent to the argument of the one or more corresponding aggregate functions in said second set of aggregate functions;

wherein said first set of aggregate functions includes a particular aggregation function to be applied to a target population, wherein said particular aggregation function is an aggregation function from a set of aggregation functions that consists of variance and standard deviation;

testing whether each aggregate function in said first set of aggregate functions is computable from one or more corresponding aggregate functions in said second set of aggregate functions, wherein said testing includes determining whether the particular materialized view includes a variance, sum, and count of a source population on which the target population is functionally dependent; and

if said materialized view satisfies each condition in said set of conditions, then rewriting said query to produce a rewritten query that references said materialized view and derives said results of said particular aggregation function for said target population from the variance, sum, and count of said source population.

2. The method of claim 1 wherein said particular aggregate function is variance.

3. The method of claim 1 wherein said particular aggregate function is standard deviation.

4. A method for processing queries, the method comprising the steps of:

receiving a query that does not reference a particular materialized view;

said query specifying a first set of one or more aggregate functions;

said particular materialized view reflecting a second set of one or more aggregate functions;

determining whether the particular materialized view satisfies each condition in a set of conditions, the set of conditions at least including;

that each aggregate function in said first set of aggregate functions be computable from one or more corresponding aggregate functions in said second set of aggregate functions; and

that the argument to each aggregate function in said first set of aggregate functions be equivalent to the argument of the one or more corresponding aggregate functions in said second set of aggregate functions; and

testing whether the argument of each aggregate function in said first set of aggregate functions is equivalent to the argument of one or more corresponding aggregate functions in said second set of aggregate functions by

a) creating a transformed version of the argument by transforming the argument of the aggregate function to a canonical form;

b) creating transformed versions of the arguments of the one or more corresponding aggregate functions by transforming the arguments of the one or more corresponding aggregate functions to a canonical form; and

c) comparing the transformed version of the argument to the transformed versions of the arguments of the one or more corresponding aggregate functions; and

if said materialized view satisfies each condition in said set of conditions, then rewriting said query to produce a rewritten query that references said materialized view.

5. The method of claim 4 wherein the step of comparing the transformed version of the argument to the transformed versions of the arguments of the one or more corresponding aggregate functions includes performing a byte-to-byte comparison of the transformed version of the argument to the transformed versions of the arguments of the one or more corresponding aggregate functions.

6. The method of claim 4 wherein the step of transforming the argument of the aggregate function to a canonical form includes sorting operands contained within the argument that are combined using a commutative operator.

7. The method of claim 4 wherein the step of creating transformed versions of the arguments of the one or more corresponding aggregate functions is performed prior to receiving said query.

8. The method of claim 7 further comprising the step of persistently storing, as metadata associated with said materialized view, the transformed versions of the arguments of the one or more corresponding aggregate functions.

9. The method of claim 7 wherein the step of creating transformed versions of the arguments of the one or more corresponding aggregate functions is performed in response to creation of said materialized view.

10. A method for rewriting queries, the method comprising the steps of:

receiving a query that specifies an outer join that has a dimension table on the child-side of the outer join and a fact table on the parent-side of the outer join;

rewriting the query to produce a rewritten query by replacing references to the fact table in the query with references to a materialized view;

wherein the materialized view includes a join between the dimension table and the fact table; and

wherein the rewritten query specifies an outer join that has the dimension table on the child side and the materialized view on the parent side.

11. The method of claim 10 wherein the join included in the materialized view is an outer join that has the dimension table on the parent-side and the fact table on the child-side.

12. The method of claim 10 wherein the join included in the materialized view is an inner join that has the dimension table on the parent-side and the fact table on the child-side.

13. The method of claim 10 wherein:

the materialized view is a summary table that contains values grouped by a particular column in the fact table;

the query specifies an aggregation that requires values grouped by a particular column in the dimension table; and

the rewritten query contains an outer join, where the particular column of the dimension table the child-side join key and a column of the summary table that contains values from the particular column of the fact table.

14. A computer-readable medium carrying one or more sequences of one or more instructions for processing queries, the one or more sequences of one or more instructions including instructions which, when executed by one or more processors, cause the one or more processors to perform the steps of:

receiving a query that does not reference a particular materialized view;

said query specifying a first set of one or more aggregate functions;

said particular materialized view reflecting a second set of one or more aggregate functions;

determining whether the particular materialized view satisfies each condition in a set of conditions, the set of conditions at least including;

that each aggregate function in said first set of aggregate functions be computable from one or more corresponding aggregate functions in said second set of aggregate functions; and

that the argument to each aggregate function in said first set of aggregate functions be equivalent to the argument of the one or more corresponding aggregate functions in said second set of aggregate functions;

wherein said first set of aggregate functions includes a particular aggregation function to be applied to a target population, wherein said particular aggregation function is an aggregation function from a set of aggregation functions that consists of variance and standard deviation;

testing whether each aggregate function in said first set of aggregate functions is computable from one or more corresponding aggregate functions in said second set of aggregate functions, wherein said testing includes determining whether the particular materialized view includes a variance, sum, and count of a source population on which the target population is functionally dependent; and

if said materialized view satisfies each condition in said set of conditions, then rewriting said query to produce a rewritten query that references said materialized view and derives said results of said particular aggregation function for said target population from the variance, sum, and count of said source population.

15. The computer-readable medium of claim 14 wherein said particular aggregate function is variance.

16. The computer-readable medium of claim 14 wherein said particular aggregate function is standard deviation.

17. A computer-readable medium carrying one or more sequences of one or more instructions for processing queries, the one or more sequences of one or more instructions including instructions which, when executed by one or more processors, cause the one or more processors to perform the steps of:

receiving a query that does not reference a particular materialized view;

said query specifying a first set of one or more aggregate functions;

said particular materialized view reflecting a second set of one or more aggregate functions;

determining whether the particular materialized view satisfies each condition in a set of conditions, the set of conditions at least including;

that each aggregate function in said first set of aggregate functions be computable from one or more corresponding aggregate functions in said second set of aggregate functions; and

that the argument to each aggregate function in said first set of aggregate functions be equivalent to the argument of the one or more corresponding aggregate functions in said second set of aggregate functions; and

testing whether the argument of each aggregate function in said first set of aggregate functions is equivalent to the argument of one or more corresponding aggregate functions in said second set of aggregate functions by

a) creating a transformed version of the argument by transforming the argument of the aggregate function to a canonical form;

b) creating transformed versions of the arguments of the one or more corresponding aggregate functions by transforming the arguments of the one or more corresponding aggregate functions to a canonical form; and

c) comparing the transformed version of the argument to the transformed versions of the arguments of the one or more corresponding aggregate functions;

if said materialized view satisfies each condition in said set of conditions, then rewriting said query to produce a rewritten query that references said materialized view.

18. The computer-readable medium of claim 17 wherein the step of comparing the transformed version of the argument to the transformed versions of the arguments of the one or more corresponding aggregate functions includes performing a byte-to-byte comparison of the transformed version of the argument to the transformed versions of the arguments of the one or more corresponding aggregate functions.

19. The computer-readable medium of claim 17 wherein the step of transforming the argument of the aggregate function to a canonical form includes sorting operands contained within the argument that are combined using a commutative operator.

20. The computer-readable medium of claim 17 wherein the step of creating transformed versions of the arguments of the one or more corresponding aggregate functions is performed prior to receiving said query.

21. The computer-readable medium of claim 20 further comprising sequences of instructions for performing the step of persistently storing, as metadata associated with said materialized view, the transformed versions of the arguments of the one or more corresponding aggregate functions.

22. The computer-readable medium of claim 20 wherein the step of creating transformed versions of the arguments of the one or more corresponding aggregate functions is performed in response to creation of said materialized view.

23. A computer-readable medium carrying one or more sequences of one or more instructions for rewriting queries, the one or more sequences of one or more instructions including instructions which, when executed by one or more processors, cause the one or more processors to perform the steps of:

receiving a query that specifies an outer join that has a dimension table on the child-side of the outer join and a fact table on the parent-side of the outer join;

rewriting the query to produce a rewritten query by replacing references to the fact table in the query with references to a materialized view;

wherein the materialized view includes a join between the dimension table and the fact table; and

wherein the rewritten query specifies an outer join that has the dimension table on the child side and the materialized view on the parent side.

24. The computer-readable medium of claim 23 wherein the join included in the materialized view is an outer join that has the dimension table on the parent-side and the fact table on the child-side.

25. The computer-readable medium of claim 23 wherein the join included in the materialized view is an inner join that has the dimension table on the parent-side and the fact table on the child-side.

26. The computer-readable medium of claim 23 wherein:

the materialized view is a summary table that contains values grouped by a particular column in the fact table;

the query specifies an aggregation that requires values grouped by a particular column in the dimension table; and

the rewritten query contains an outer join, where the particular column of the dimension table the child-side join key and a column of the summary table that contains values from the particular column of the fact table.