Using materialized view to process a related query containing a one to many lossless join

• US 6,449,609 B1
• Filed: 12/28/1998
• Issued: 09/10/2002
• Est. Priority Date: 12/28/1998
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle Corporation
• Inventors: Witkowski, Andrew
• Primary Examiner(s): JUNG, DAVID YIUK
• Application Number:
  US09/221,647
• Time in Patent Office: 1,352 Days
• Field of Search: 707/1-206, 711/1-6, 711/200-203, 709/1, 709/100, 717/1-11, 717/140-148
• 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 apparatus for transforming queries is described. According to an aspect of the present invention, a select-project-join query Q that specifies at least one semijoin is rewritten into another query that references the materialized view M. A common section shared by the select-project-join query and the materialized view includes the tables being semijoined.

Claims

1. A method of transforming a first query, the method comprising the steps of:

receiving said first query, wherein said first query specifies a first semijoin between a first table and a second table;

determining whether a common section is shared between a view and said first query, wherein a view definition for said view includes a join between said common section and a materialized view delta, said common section including said first semijoin;

determining whether said join between said common section and said materialized view delta is lossless with respect to said common section; and

if said join between said common section and said materialized view delta is lossless with respect to said common section, then transforming said first query into a second query that accesses data through said view, said second query returning the set of data specified by said first query.

2. The method of claim 1, wherein said view is a materialized view and the step of transforming said first query includes transforming said first query into a second query that references said materialized view.

3. The method of claim 1, wherein the step of determining whether a common section is shared includes determining whether said common section is shared with a view definition that specifies a join of three or more tables that include said first table and said second table.

4. The method of claim 1, wherein the step of receiving said first query includes receiving a query that defines a first semijoin operation using an IN operator that operates upon a query block of said second table.

5. The method of claim 1, wherein the step of receiving said first query includes receiving a query that defines a first semijoin operation using an EXISTS operator that operates upon a query block of said second table.

6. The method of claim 1, wherein rows presented from said view each correspond to one row from said first table, and the step of transforming said first query into a second query further includes the steps of:

determining whether said view definition references one or more distinguishing columns, that for each of said rows presented by said view, uniquely identify the one row from said first table that corresponds to said row presented by said view; and

if said view definition references one or more distinguishing columns, then rewriting said first query to include a sub-query that accesses data through said view, said sub-query including a DISTINCT operator that operates upon a list of columns that include said one or more identifying columns.

7. The method of claim 6, wherein the step of determining whether said view references one or more distinguishing columns further includes determining whether said view references a row-id column, wherein:

said row-id column corresponds to row-ids that are each associated with a row from said first table; and

each row-id associated with a row from said first table uniquely identifies the row from said first table relative to all rows of said first table.

8. The method of claim 6, wherein the step of determining whether said view references one or more distinguishing columns includes determining whether said view references a column that corresponds to a unique key column of said first table.

9. The method of claim 1, wherein the step of determining whether said join between said common section and said materialized view delta is lossless includes determining whether said view definition defines an outer join between a third table and said second table.

10. The method of claim 1, wherein the step of determining whether said join between said common section and said materialized view delta is lossless includes determining whether said view definition defines a join between said second table and a third table, said second table being referentially constrained to said third table.

11. The method of claim 1, wherein:

the step of receiving a first query includes receiving a first query that specifies a first semijoin and a second semijoin; and

the step of whether said common section that includes said first and second semijoins is shared between said view and said first query.

12. The method of claim 11, wherein the step of transforming said first query includes transforming said first query into a second query that includes an antijoin filter that filters antijoin rows from said view.

13. A computer-readable medium carrying one or more sequences of one or more instructions for transforming a first query, wherein the execution of the one or more sequences of the one or more instructions by one or more processors, causes the one or more processors to perform the steps of:

receiving said first query, wherein said first query specifies a first semijoin between a first table and a second table;

determining whether a common section is shared between a view and said first query, wherein a view definition for said view includes a join between said common section and a materialized view delta, said common section including said first semijoin;

determining whether said join between said common section and said materialized view delta is lossless with respect to said common section; and

if said join between said common section and said materialized view delta is lossless with respect to said common section, then transforming said first query into a second query that accesses data through said view, said second query returning the set of data specified by said first query.

14. The computer-readable medium of claim 13, wherein said view is a materialized view and the step of transforming said first query includes transforming said first query into a second query that references said materialized view.

15. The computer-readable medium of claim 13 wherein the step of determining whether a common section is shared includes determining whether said common section is shared with a view definition that specifies a join of three or more tables that include said first table and said second table.

16. The computer-readable medium of claim 13 wherein the step of receiving said first query includes receiving a query that defines a first semijoin operation using an IN operator that operates upon a query block of said second table.

17. The computer-readable medium of claim 13 wherein the step of receiving said first query includes receiving a query that defines a first semijoin operation using an EXISTS operator that operates upon a query block of said second table.

18. The computer-readable medium of claim 13 wherein rows presented from said view each correspond to one row from said first table, and the step of transforming said first query into a second query further includes the steps of:

determining whether said view definition references one or more distinguishing columns, that for each of said rows presented by said view, uniquely identify the one row from said first table that corresponds to said row presented by said view; and

if said view definition references one or more distinguishing columns, then rewriting said first query to include a sub-query that accesses data through said view, said sub-query including a DISTINCT operator that operates upon a list of columns that include said one or more identifying columns.

19. The computer-readable medium of claim 18, wherein the step of determining whether said view references one or more distinguishing columns further includes determining whether said view references a row-id column, wherein:

said row-id column corresponds to row-ids that are each associated with a row from said first table; and

each row-id associated with a row from said first table uniquely identifies the row from said first table relative to all rows of said first table.

20. The computer-readable medium of claim 18, wherein the step of determining whether said view references one or more distinguishing columns includes determining whether said view references a column that corresponds to a unique key column of said first table.

21. The computer-readable medium of claim 13, wherein:

the step of receiving a first query includes receiving a first query that specifies a first semijoin and a second semijoin; and

the step of determining whether a common section is shared includes determining whether said common section that includes said first and second semijoins is shared between said view and said first query.

22. The computer-readable medium of claim 21, wherein the step of transforming said first query includes transforming said first query into a second query that includes an antijoin filter that filters antijoin rows from said view.

23. The computer-readable medium of claim 13, wherein the step of determining whether said join between said common section and said materialized view delta is lossless includes determining whether said view definition defines an outer join between a third table and said second table.

24. The computer-readable medium of claim 13, wherein the step of determining whether said join between said common section and said materialized view delta is lossless includes determining whether said view definition defines a join between said second table and a third table, said second table being referentially constrained to said third table.