Using a materialized view to process a related query containing an antijoin
First Claim
Patent Images
1. A method of transforming a first query, the method comprising the steps of:
- receiving said first query, wherein said first query defines an antijoin between a left table and a right 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 an outerjoin between said left table and said right table, said left table being the outer table of said outerjoin;
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 Assignments
0 Petitions
Accused Products
Abstract
A method and apparatus for transforming queries is provided. A select-project-join query Q that specifies an antijoin is rewritten into another query that references the materialized view M. The common section includes the tables being antijoined, and may be one-to-many lossless with respect to the join with the materialized view delta.
51 Citations
28 Claims
-
1. A method of transforming a first query, the method comprising the steps of:
-
receiving said first query, wherein said first query defines an antijoin between a left table and a right 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 an outerjoin between said left table and said right table, said left table being the outer table of said outerjoin;
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. - View Dependent Claims (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13)
if said join between said common section and said material view delta is not one-to-one, then;
determining whether said view selects one or more distinguishing columns of the left table, that for each of said rows presented by said view, uniquely identify the one row from said left table that corresponds to said row presented by said view; and
if said view selects one or more distinguishing columns of the left table, then rewriting said received query to include a query block that references said view, said query block including a DISTINCT operator that operates upon a list of columns that include said one or more distinguishing columns.
-
-
7. The method of claim 6, wherein the step of determining whether said view selects one or more distinguishing columns further includes determining whether said view selects a rowid pseudo column of said left table.
-
8. The method of claim 6, wherein:
-
said right table includes a rowid pseudo column;
said received first query references a left join column for said antijoin;
said received query includes a first SELECT list; and
the step of transforming said received query into a transformed query further includes the step of if said join between said common section and said material view delta is not one-to-one, then transforming said received query into a transformed query with an outer query block that includes;
(1) a second SELECT list that includes said first SELECT list of the query, (2) a FROM list that references said view in a query block, said query block including;
(a) a third SELECT list that includes a distinct operator operating upon (i) the left table rowid pseudo column, and (ii) the one or more columns referenced by said first SELECT list, and (b) a WHERE clause that includes a first condition that is satisfied when said rowid pseudo column of said right table equals NULL, and the left join column does not equal NULL.
-
-
9. The method of claim 8, wherein:
-
said received query includes a right table query block with a WHERE condition; and
the step of transforming said received query into a transformed query if said join between said common section and said material view delta is one-to-one, further includes transforming said received query into a transformed query whose WHERE clause includes a second condition that is satisfied when both said rowid pseudo column of the right table does not equal NULL and the WHERE condition of the right table query block is not satisfied.
-
-
10. The method of claim 5, wherein:
-
said right table includes a rowid pseudo column;
said received first query references a left join column for said antijoin;
said received query includes a first SELECT list; and
the step of transforming said received query into a transformed query further includes the step of if said join between said common section and said material view delta is one-to-one, then transforming said received query into a transformed query that includes;
(1) a second SELECT list that includes said first SELECT list, (2) a FROM list that includes said view, (3) a WHERE clause that includes a first condition that is satisfied when said rowid pseudo column of said right table equals NULL, and said left join column does not equal NULL.
-
-
11. The method of claim 10, wherein:
-
said received query includes a right table query block with a WHERE condition; and
the step of transforming said received query into a transformed query with an outer query block if said join between said common section and said material view delta is not one-to-one, further includes transforming said received query into a transformed query with an outer query block whose WHERE clause includes a second condition that is satisfied when both said rowid pseudo column of the right table does not equal NULL and the WHERE condition of the right table query block is not satisfied.
-
-
12. 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 left or right tables are outer joined to a third table.
-
13. 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 one of left and right tables is referentially constrained to said third table.
-
14. 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 causes the one or more processors to perform the steps of:
-
receiving said first query, wherein said first query defines an antijoin between a left table and a right 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 an outerjoin between said left table and said right table, said left table being the outer table of said outerjoin;
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. - View Dependent Claims (15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26)
if said join between said common section and said material view delta is not one-to-one, then;
determining whether said view selects one or more distinguishing columns of the left table, that for each of said rows presented by said view, uniquely identify the one row from said left table that corresponds to said row presented by said view; and
if said view selects one or more distinguishing columns of the left table, then rewriting said received query to include a query block that references said view, said query block including a DISTINCT operator that operates upon a list of columns that include said one or more distinguishing columns.
-
-
20. The computer-readable media of claim 19, wherein the step of determining whether said view selects one or more distinguishing columns further includes determining whether said view selects a rowid pseudo column of said left table.
-
21. The computer-readable media of claim 20, wherein:
-
said right table includes a rowid pseudo column;
said received first query references a left join column for said antijoin;
said received query includes a first SELECT list; and
the step of transforming said received query into a transformed query further includes the step of if said join between said common section and said material view delta is not one-to-one, then transforming said received query into a transformed query with an outer query block that includes;
(1) a second SELECT list that includes said first SELECT list of the query, (2) a FROM list that references said view in a query block, said query block including;
(a) a third SELECT list that includes a distinct operator operating upon (i) the left table rowid pseudo column, and (ii) the one or more columns referenced by said first SELECT list, and (b) a WHERE clause that includes a first condition that is satisfied when said rowid pseudo column of said right table equals NULL, and the left join column does not equal NULL.
-
-
22. The computer-readable media of claim 21, wherein:
-
said received query includes a right table query block with a WHERE condition; and
the step of transforming said received query into a transformed query with an outer query block if said join between said common section and said material view delta is not one-to-one, further includes transforming said received query into a transformed query with an outer query block whose WHERE clause includes a second condition that is satisfied when both said rowid pseudo column of the right table does not equal NULL and the WHERE condition of the right table query block is not satisfied.
-
-
23. The computer-readable media of claim 18, wherein:
-
said right table includes a rowid pseudo column;
said received first query references a left join column for said antijoin;
said received query includes a first SELECT list; and
the step of transforming said received query into a transformed query further includes the step of if said join between said common section and said material view delta is one-to-one, then transforming said received query into a transformed query that includes;
(1) a second SELECT list that includes said first SELECT list, (2) a FROM list that includes said view, (3) a WHERE clause that includes a first condition that is satisfied when said rowid pseudo column of said right table equals NULL, and said left join column does not equal NULL.
-
-
24. The computer-readable media of claim 23, wherein:
-
said received query includes a right table query block with a WHERE condition; and
the step of transforming said received query into a transformed query if said join between said common section and said material view delta is one-to-one, further includes transforming said received query into a transformed query whose WHERE clause includes a second condition that is satisfied when both said rowid pseudo column of the right table does not equal NULL and the WHERE condition of the right table query block is not satisfied.
-
-
25. The computer-readable media of claim 14, wherein the step of determining whether said join between said common section and said materialized view delta is lossless includes determining whether said left or right tables are outer joined to a third table.
-
26. The computer-readable media of claim 14, wherein the step of determining whether said join between said common section and said materialized view delta is lossless includes determining whether said one of left and right tables is referentially constrained to said third table.
-
27. A database system comprising:
-
a processor;
a memory coupled to said processor;
said processor configured to receive a first query, wherein said first query defines an antijoin between a left table and a right table;
a view definition defining a view;
said processor configured to determine whether a common section is shared between said view and said first query, wherein said view definition specifies a join between said common section and a materialized view delta, said common section including an outerjoin between said left table and said right table, said left table being the outer table of said outerjoin;
said processor configured to determine whether said join between said common section and said materialized view delta is lossless with respect to said common section; and
said processor configured to, if said join between said common section and said materialized view delta is lossless with respect to said common section, transform 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. - View Dependent Claims (28)
-
Specification