Rewriting a query in terms of a summary based on functional dependencies and join backs, and based on join derivability

• US 6,496,819 B1
• Filed: 12/28/1998
• Issued: 12/17/2002
• Est. Priority Date: 12/28/1998
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle Corporation
• Inventors: Bello, Randall, Finnerty, James, Witkowski, Andrew, Ziauddin, Mohamed
• Primary Examiner(s): JUNG, DAVID YIUK
• Application Number:
  US09/221,641
• Time in Patent Office: 1,450 Days
• Field of Search: 707/1-206, 711/1-6, 711/200, 711/203, 709/1, 709/100, 717/1-11
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24537 of operators
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation

Abstract

A method and system is provided for processing queries. Specifically, techniques are provided for handling a query that does not reference a particular materialized view, where the query requires access to values from a particular column not contained in the materialized view. A technique is also provided for processing a query that does not reference a particular materialized aggregate view, where the materialized aggregate view specifies an outer join between a child table and a parent table and the query specifies a particular type of join between the child table and the parent table, where the particular type of join is one of an inner join, an anti-join and a semi-join. The query is rewritten to produce a rewritten query that accesses the materialized aggregate view to produce data required by the query. A technique is also provided for processing a query that does not reference a particular materialized view and that specifies that results are to be grouped by a first set of one or more columns, where the materialized view reflects data that is grouped by a second set of one or more columns. If each column in the first set of columns either exactly matches a column in the second set of columns, is functionally dependent on another column in the first set of columns, or is functionally dependent on a column in the second set of columns, then the query is rewritten to produce a rewritten query that references the materialized view.

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 requiring access to values from a particular column not contained in said materialized view;

determining whether the particular materialized view satisfies each condition in a set of conditions, the set of conditions at least including a condition that said particular column is functionally dependent on a column that does belong to said materialized view; 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 joins said materialized view back to a join-back table that contains said particular column.

2. The method of claim 1 wherein said particular column is a group by column specified in said query.

3. The method of claim 1 wherein:

the rewritten query joins back to said join-back table based on a join-back key;

the method further comprises the step of determining whether said join-back key contains unique values; and

if said join-back key contains unique values, then rewriting said query by replacing references made in said particular query to a particular table with references to the materialized view.

4. The method of claim 1 wherein, if said join-back key does not contain unique values, then rewriting said query by performing the steps of:

replacing references made in said particular query to said particular table with references to the materialized view; and

replacing references made in said particular query to said join-back table with references to a view that extracts distinct values from said join-back key.

5. The method of claim 1 further comprising the steps of:

storing, in a database, functional dependency metadata; and

reading said functional dependency metadata to determine whether said particular column is functionally dependent on a column that does belong to said materialized view.

6. The method of claim 5 wherein the step of storing functional dependency metadata includes storing metadata that indicates a functional dependency between two columns of the same table.

7. A method of processing queries, the method comprising the steps of:

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

said materialized aggregate view specifying an outer join between a child table and a parent table;

said query specifying a particular type of join between said child table and said parent table, wherein said particular type of join is one of an inner join, an anti-join and a semi-join; and

rewriting said query to produce a rewritten query that accesses said materialized aggregate view to produce data required by said query.

8. The method of claim 7 wherein:

the particular type of join is one of either a semi-join and an inner join;

the method further includes the step of using anti-join markers in the materialized view to filter anti-join rows from the materialized aggregate view to produce the data required by said query.

9. The method of claim 7 wherein:

the particular type of join is an anti-join; and

the method further includes the step of using anti-join markers in the materialized view to filter rows that are not anti-join rows from the materialized aggregate view to produce the data required by said query.

10. The method of claim 7 wherein the step of rewriting said query to produce a rewritten query that accesses said materialized aggregate view includes rewriting said query to use a scale factor to remove any effect, on aggregate values in said materialized aggregate view, of any duplicates produced by said outer join.

11. A method of processing queries, the method comprising the steps of:

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

said materialized aggregate view specifying an inner join between a child table and a parent table;

said query specifying a semi-join between said child table and said parent table; and

rewriting said query to produce a rewritten query that accesses said materialized aggregate view to produce data required by said query.

12. The method of claim 11 wherein the step of rewriting said query to produce a rewritten query that accesses said materialized aggregate view includes the step of rewriting said query to use a scale factor to reduce any effect on aggregate values contained in said materialized aggregate view of any duplicates.

13. 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 that results are to be grouped by a first set of one or more columns;

said materialized view reflecting data that is grouped by a second set of one or more columns;

determining whether each column in said first set of columns either

a) exactly matches a column in said second set of columns,

b) is functionally dependent on another column in said first set of columns, or

c) is functionally dependent on a column in said second set of columns;

if each column in said first set of columns either exactly matches a column in said second set of columns, is functionally dependent on another column in said first set of columns, or is functionally dependent on a column in said second set of columns, then rewriting said query to produce a rewritten query that references said materialized view.

14. The method of claim 13 wherein the query includes a group by clause that specifies said first set of one or more columns, the method further comprising the steps of:

determining whether all columns in said first set of one or more columns either

a) match exactly with a column in said second set of one or more columns, or

b) functionally depend on some other column in said first set of one or more columns;

if all columns in said first set of one or more columns either match exactly with a column in said second set of one or more columns or functionally depend on some other column in said first set of one or more columns, then rewriting said query to produce a rewritten query that does not include said group by clause.

15. The method of claim 13 further comprising the steps of:

determining whether a particular set of conditions is satisfied, said particular set of conditions including a condition that;

a) at least one column in the first set of one or more columns is functionally dependent on a column in the second set of one or more columns, or

b) there is at least one column in the second set of one or more columns that neither

b1) exactly matches any column in said first set of one or more columns, nor

b2) functionally depends on any other column in said second set of one or more columns;

if said particular set of conditions is satisfied, then rewriting said query to produce a rewritten query that rolls up values contained in said particular materialized view.

16. A computer-readable medium carrying one or more sequences of instructions for processing queries, wherein execution of the one or more sequences of instructions by one or more processors causes the one or more processors to perform the steps of:

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

said query requiring access to values from a particular column not contained in said materialized view;

determining whether the particular materialized view satisfies each condition in a set of conditions, the set of conditions at least including a condition that said particular column is functionally dependent on a column that does belong to said materialized view; 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 joins said materialized view back to a join-back table that contains said particular column.

17. The computer-readable medium of claim 16 wherein said particular column is a group by column specified in said query.

18. The computer-readable medium of claim 16 wherein:

the rewritten query joins back to said join-back table based on a join-back key;

the steps further comprise;

determining whether said join-back key contains unique values; and

if said join-back key contains unique values, then rewriting said query by replacing references made in said particular query to a particular table with references to the materialized view.

19. The computer-readable medium of claim 16, wherein the steps further include:

if said join-back key does not contain unique values, then rewriting said query by performing the following steps of;

replacing references made in said particular query to said particular table with references to the materialized view; and

replacing references made in said particular query to said join-back table with references to a view that extracts distinct values from said join-back key.

20. The computer-readable medium of claim 16, wherein the steps further comprise:

storing, in a database, functional dependency metadata; and

reading said functional dependency metadata to determine whether said particular column is functionally dependent on a column that does belong to said materialized view.

21. The computer-readable medium of claim 20 wherein the step of storing functional dependency metadata includes storing metadata that indicates a functional dependency between two columns of the same table.

22. A computer-readable medium carrying one or more sequences of instructions for processing queries, wherein execution of the one or more sequences of instructions by one or more processors causes the one or more processors to perform the steps of:

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

said materialized aggregate view specifying an outer join between a child table and a parent table;

said query specifying a particular type of join between said child table and said parent table, wherein said particular type of join is one of an inner join, an anti-join and a semi-join; and

rewriting said query to produce a rewritten query that accesses said materialized aggregate view to produce data required by said query.

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

the particular type of join is one of either a semi-join and an inner join;

the steps further include using anti-join markers in the materialized view to filter anti-join rows from the materialized aggregate view to produce the data required by said query.

24. The method of claim 22 wherein:

the particular type of join is an anti-join; and

the steps further include using anti-join markers in the materialized view to filter rows that are not anti-join rows from the materialized aggregate view to produce the data required by said query.

25. The computer-readable medium of claim 22, wherein the step of rewriting said query to produce a rewritten query that accesses said materialized aggregate view includes rewriting said query to use a scale factor to remove any effect, on aggregate values in said materialized aggregate view, of any duplicates produced by said outer join.

26. A computer-readable medium carrying one or more sequences of instructions for processing queries, wherein execution of the one or more sequences of instructions by one or more processors causes the one or more processors to perform the steps of:

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

said materialized aggregate view specifying an inner join between a child table and a parent table;

said query specifying a semi-join between said child table and said parent table; and

rewriting said query to produce a rewritten query that accesses said materialized aggregate view to produce data required by said query.

27. The computer-readable medium of claim 26, wherein the step of rewriting said query to produce a rewritten query that accesses said materialized aggregate view includes the step of rewriting said query to use a scale factor to reduce any effect on aggregate values contained in said materialized aggregate view of any duplicates.

28. A computer-readable medium carrying one or more sequences of instructions for processing queries, wherein execution of the one or more sequences of instructions by one or more processors causes the one or more processors to perform the steps of:

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

said query specifying that results are to be grouped by a first set of one or more columns;

said materialized view reflecting data that is grouped by a second set of one or more columns;

determining whether each column in said first set of columns either

a) exactly matches a column in said second set of columns,

b) is functionally dependent on another column in said first set of columns, or

c) is functionally dependent on a column in said second set of columns;

if each column in said first set of columns either exactly matches a column in said second set of columns, is functionally dependent on another column in said first set of columns, or is functionally dependent on a column in said second set of columns, then rewriting said query to produce a rewritten query that references said materialized view.

29. The computer-readable medium of claim 28, wherein the query includes a group by clause that specifies said first set of one or more columns, wherein the steps further comprise:

determining whether all columns in said first set of one or more columns either

a) match exactly with a column in said second set of one or more columns, or

b) functionally depend on some other column in said first set of one or more columns;

if all columns in said first set of one or more columns either match exactly with a column in said second set of one or more columns or functionally depend on some other column in said first set of one or more columns, then rewriting said query to produce a rewritten query that does not include said group by clause.

30. The computer-readable medium of claim 28, the steps further comprising:

determining whether a particular set of conditions is satisfied, said particular set of conditions including a condition that;

a) at least one column in the first set of one or more columns is functionally dependent on a column in the second set of one or more columns, or

b) there is at least one column in the second set of one or more columns that neither

b1) exactly matches any column in said first set of one or more columns, nor

b2) functionally depends on any other column in said second set of one or more columns;

if said particular set of conditions is satisfied, then rewriting said query to produce a rewritten query that rolls up values contained in said particular materialized view.