Materialized View Rewriting Technique for Unilateral Outer Join Queries

Abstract

Use a materialized view (MV) to rewrite a query based on a unilateral outer join, where the definition of the MV includes joins but does not include the filter predicates from the query. The rewritten query nullifies the data in the MV that does not satisfy the filter predicates and comes from the tables including the matching tables. To improve the accuracy of the query results, certain rows are removed from the intermediate results of the query. To facilitate revising the query results for improved accuracy, the MV includes unique columns from all the tables included, and also includes an indicator column indicating whether a given row of the MV is a row of the inner join type or a row of the anti-join type. The rewritten query adjusts the indicator values in the indicator column of the MV rows that do not satisfy the filter to reflect the indicator values of the modified anti-join type. Based on the modified indicator values and the unique columns from all the tables included, the accuracy of the query results is achieved.

Description

Technical Field

[0001] The present invention relates to query rewrite using materialized views, and more particularly to using materialized views to rewrite queries based on one-sided outer-joins, where the definition of the materialized view does not include the filter predicates applied in the query. Background Art

[0002] Materialized views can be a powerful tool for optimizing query execution in a database management system. Specifically, a materialized view (MV) that captures the result of one or more expensive operations can be used to rewrite a query that requires execution of that one or more expensive operations, which eliminates the need for the expensive operations associated with executing the query. However, the creation of an MV that captures complex and / or expensive database operations is generally very expensive. Therefore, it is beneficial to formulate the MV such that as many queries as possible can utilize the MV to execute the query.

[0003] The join operation to compute from library tables can be particularly expensive. Therefore, an MV that materializes the join operation has high potential value for optimizing join-based queries. However, very few database vendors allow an MV definition to include a one-sided outer-join operation (such as a left outer join or a right outer join). At least one existing data management system allows a one-sided outer-join operation in the MV definition. However, in such a system, a query can only be rewritten to use an MV based on a one-sided outer-join when:

[0004] (a) The MV includes sufficient data to answer the query; and

[0005] (b) The predicates in the query and the predicates in the MV definition exactly match, with certain minor exceptions.

[0006] In other words, such a database management system does not allow a query to be rewritten to use an MV that is more general (with respect to the filter predicates) than the query being rewritten.

[0007] However, including the filter predicates in the MV definition limits the applicability of the MV, thus limiting their value in the database management system. Therefore, it is beneficial to enable the rewriting of a query using an MV that is more general (i.e., has a definition that does not include the one or more filter predicates) than a one-sided outer-join-based query that includes one or more filter predicates.

[0008] The methods described in this section are methods that can be sought, but not necessarily methods that have been previously envisioned or sought. Therefore, unless otherwise indicated, no method described in this section alone should be assumed to be eligible as prior art solely by virtue of its inclusion in this section. Brief Description of the Drawings

[0009] In the accompanying drawings:

[0010] Figure 1 A flowchart depicting the rewriting of a query involving a unary outer-join predicate and a filter predicate using a more general materialized view that materializes the unary outer-join.

[0011] Figure 2 Is a block diagram of a database management system.

[0012] Figure 3 A flowchart depicting the execution of the rewritten query that utilizes the materialized view and involves a unary join operation of a many-to-many type or a one-to-many type.

[0013] Figure 4 Depicts an example content of the materialized view.

[0014] Figure 5 Depicts an example intermediate result for a query rewritten to use the materialized view.

[0015] Figure 6 A flowchart depicting the execution of the rewritten query that utilizes the materialized view and involves a unary join operation of a many-to-one type.

[0016] Figure 7 Depicts an example content of the materialized view, as well as an example intermediate result for a query rewritten to use the materialized view.

[0017] Figure 8 Is a block diagram of a computer system on which embodiments can be implemented.

[0018] Figure 9 Depicts a software system that can be used in the embodiments. Detailed Description

[0019] In the following description, for purposes of explanation, numerous specific details are set forth in order to provide a thorough understanding of the present invention. It will be apparent, however, that the present invention may be practiced without these specific details. In other instances, well-known structures and devices are shown in block diagram form in order to avoid unnecessarily obscuring the present invention.

[0020] General Overview

[0021] Techniques for rewriting queries involving a unilateral outer join operation (i.e., a left or right outer join) using a materialized view (MV) will be described herein, where the MV (a) materializes the unilateral outer join operation and (b) is more general than the query. Specifically, the MV is more general than the query when one or more filter predicates applied in the query are not applied in the definition of the MV. Thus, using the techniques described herein, an MV that materializes a unilateral outer join operation can be written such that the MV definition does not include (or includes a uniform) filter predicate. These more general join-based MVs can be used to accelerate the execution of a greater variety of queries that use the materialized join.

[0022] Outer join

[0023] An outer join is a JOIN operation that joins two tables based on a specified join predicate and retains the rows of one or both of the two tables that do not join with any rows of the other table. A unilateral outer join retains all eligible rows, including rows that do not match the first ("include-all") table, and joins them with NULL rows in the shape of the second ("include-matching") table. (The eligible rows of the include-all table are the rows that satisfy the (one or more) filter predicates (if any) applicable to that include-all table.) Thus, in a unilateral outer join, the rows of the table on one side of the join are always included in the result, while the rows of the table on the other side of the join are included only if they match the join key of at least one row from the other side of the join. The side of the table whose rows are all included in the result of the outer join is referred to herein as the "include-all" table. The side of the table whose rows are included in the result only if they match the join key of at least one row from the other side of the join is referred to herein as the "include-matching" table. Thus, in a left outer join, the left table is the include-all table and the right table is the include-matching table. Conversely, in a right outer join, the right table is the include-all table and the left table is the include-matching table. An inner join is a join operation that joins two tables based on a specified join predicate and retains only those rows of the two tables that join with one or more rows of the other table.

[0024] The following example query "Q0" illustrates a simple query with a left outer join. Also included below are two example tables on which Q0 is defined.

[0025] (Q0)

[0026] SELECT Tab1.A,Tab1.B,Tab2.C,Tab2.D

[0027] FROM Tab1,Tab2

[0028] WHERE Tab1.B = Tab2.C(+);

[0029] Tab1

[0030] A B 1 4 2 4 3 6 4 5

[0031] Tab2

[0032] C D 4 8 6 9 6 1 7 2

[0033] The following result set shows the results of Q0 based on the example tables Tab1 (the joined "include all" table) and Tab2 (the joined "include matching" table). All rows of Tab1 are retained in the result set. Additionally, some rows of Tab1 are duplicated in the result set due to the many-to-many nature of the join. Specifically, the third row of Tab1 is duplicated because it joins with two rows of Tab2. For the last row of Tab1 that has no matching rows in Tab2, the columns of Tab2 are given NULL values (i.e., are empty) in the result.

[0034] A B C D 1 4 4 8 2 4 4 8 3 6 6 9 3 6 6 1 4 5 NULL NULL

[0035] Note that if there is a unique constraint on Tab2.C in the join predicate of Q0, the left outer join on Tab1.B = Tab2.C will be a many-to-one type of join. In this case, there will be no duplicates of the Table 1 rows.

[0036] Materialized view based on an outer join

[0037] A materialized view based on an outer join is generated based on the join predicate between two tables. In this document, a specific example using a left outer join operation will be given to illustrate the unilateral outer join operation. However, the techniques described herein are equally applicable to right outer join operations. Additionally, examples using joins between two tables are used in this document to describe the embodiments. However, the techniques described herein are equally applicable to joins between any number of tables.

[0038] Consider two tables T1 and T2, and a left outer join predicate for an example materialized view: T1.x = T2.y(+). In this example, in the example left outer join predicate, T1 is the "left" table (and also the "include all" table), and T2 is the "right" table (and also the "include matching" table). Note that the "(+)" notation is placed near the reference to the table in the join, and all rows from that table need not be included in the join result (hence, the "include matching" label on the right table). Thus, the example join predicate is a left outer join because the rows in the right table are indicated as optional, and every row in the left table is retained regardless of whether each row in the left table joins with any row in the right table (hence, the "include all" label on the left table). When a row from the left table does not join with any row in the right table, as indicated in the last row of the example result set above Q0, the values of the columns originating from the right table are null in the resulting row.

[0039] When there are no uniqueness constraints on T1.x or T2.y, a left outer join with an equality-based join predicate is considered many-to-many. In the case of a many-to-many left outer join, the rows of both tables may be duplicated in the left outer join result. If T1.x is unique for the left table T1, the left outer join is considered one-to-many, and the join result may include only the duplicated rows of the left table. If T2.y is unique for the right table T2, the left outer join is considered many-to-one, and the join result may include only the duplicated rows of the right table (and not copies of the rows of the left table). If both T1.x and T2.y are unique, the left outer join is considered one-to-one, and the rows of both tables cannot be duplicated.

[0040] The rows from the include all table in the result set of a unilateral outer join are included in the result set regardless of whether they match one or more rows of the include matching table. Useful for the techniques described herein is knowing whether the include matching rows of a given row in the join result set match. Thus, according to an embodiment, an MV definition based on a left outer join involves an indicator value (IVal) column for each left outer join predicate, which may also be referred to as an inner join indicator (IJI) column. An IVal of 1 indicates a row of the inner join type (i.e., an include all table row that matches an include matching table row), and an IVal of 0 indicates a row of the anti-join type (i.e., an include all table row that does not match an include matching table row). The indicator values in the IVAL column can be used to derive the inner join set from the materialized outer join set.

[0041] Thus, in the result set of a unilateral outer join operation, each row including all table rows is either (A) reflected in one or more rows of the result set (because it joins with one or more rows of the included match table), or (B) included once in the result set (because the row including all table rows does not match any row of the included match table). In other words, in the unilateral outer join result set, the row including all table rows should appear as part of either (A) one or more inner join type rows (Ival = 1), or (B) exactly one anti-join type row (Ival = 0) in the result set. The result set that meets these qualifications is referred to as "integrated" in this document. The incorrect result set (i.e., does not meet these qualifications, for example, because the row including all table rows (a) appears as part of both an inner join type row and an anti-join type row, and / or (b) appears as part of multiple anti-join type rows) is referred to as "unintegrated" in this document.

[0042] Rewriting of Queries Based on Outer Joins

[0043] Using the techniques described in this document, when a query based on a unilateral outer join applies a filter predicate to the included match table of the join (such as the right table of a left outer join), the query is rewritten to use an MV whose definition does not include the filter predicate.

[0044] Specifically, the following represents an embodiment of a six-stage technique for rewriting a query using a many-to-many or one-to-many unilateral outer join based on an MV.

[0045] 1. Apply the filter predicate defined on the inner join table or the table including all rows in the query to the MV.

[0046] 2. For each row of the MV, and for each unilateral outer join in the MV: If it is an inner join row and the row does not meet the filter predicate specified on the included match table in the query, then (A) make the columns from the included match table null, (B) make the aggregate functions defined on the columns of the included match table null, and (C) mark the row as a modified anti-join (MAJ) row.

[0047] 3. For each partition formed by the list of unique columns of the table including all rows and the inner join table: If the partition only involves MAJ rows, place a marker on one of the MAJ rows.

[0048] 4. Filter out all unmarked MAJ rows.

[0049] 5. Perform the filtering and join with other tables not referenced in the MV definition if needed.

[0050] 6. Recalculate the grouping and aggregation on the grouping (group-by) columns specified in the query.

[0051] In addition, the following presents an embodiment of a four-stage technique for rewriting a query using a many-to-one unilateral outer join based on an MV.

[0052] 1. Apply the filter predicates defined on all tables and inner join tables in the query to the MV filter.

[0053] 2. For each row of the MV and for each unilateral outer join in the MV: If it is an inner join row and the row does not satisfy the filter predicates specified on the included match tables in the query, then (A) set the columns from the included match tables to null, and (B) set the aggregate functions defined on the columns of the included match tables to null.

[0054] 3. Perform the filtering and join with other tables not referenced in the MV definition if needed.

[0055] 4. Recalculate the grouping and aggregation on the grouping columns specified in the query.

[0056] Each stage of the above two techniques will be described in more detail below.

[0057] Figure 1 FIG. 100 depicts a flowchart for rewriting a query involving a unilateral outer join predicate and a filter predicate using a more general materialized view that materializes the unilateral outer join. At step 102, the database management system executes a query on multiple tables in a database managed by the database management system, where the query involves a unilateral outer join operation on all tables and included match tables, and where the query further involves a filter on the included match tables. For example, the database management system 210( Figure 2 ) receives the following query "Q1" for tables T1 and T2 stored in database 232 on storage device 230 from a database client running on client device 240:

[0058] (Q1)

[0059] SELECT T1.b1,T2.c2,SUM(T1.d1),COUNT(T1.d1)

[0060] FROM T1,T2

[0061] WHERE T1.a1=T2.a2(+) and T2.b2(+)=6

[0062] GROUP BY T1.b1,T2.c2;

[0063] The following example query is the ANSI syntax counterpart of Q1, where "Q1_ANSI_LEFT" is the representation of the left outer join in Q1, and "Q1_ANSI_RIGHT" is the representation of the outer join in Q1 that is expressed as a right outer join. That is, Q1, Q1_ANSI_LEFT, and Q1_ANSI_RIGHT are semantically equivalent.

[0064] (Q1_ANSI_LEFT)

[0065] SELECT T1.b1,T2.c2,SUM(T1.d1),COUNT(T1.d1)

[0066] FROM T1 LEFT OUTER JOIN T2 ON(T1.a1=T2.a2 and T2.b2=6)

[0067] GROUP BY T1.b1,T2.c2;

[0068] (Q1_ANSI_RIGHT)

[0069] SELECT T1.b1,T2.c2,SUM(T1.d1),COUNT(T1.d1)

[0070] FROM T2 RIGHT OUTER JOIN T1 ON(T1.a1=T2.a2 and T2.b2=6)

[0071] GROUP BY T1.b1,T2.c2;

[0072] As indicated by the join predicate T1.a1=T2.a2(+), Q1 involves a left outer join of the left table T1 ("include all" table) and the right table T2 ("include matching" table). The result of Q1 includes b1 from table T1, c2 from table T2, the sum type aggregation of d1 from table T1, and the count type aggregation of d1. In addition, as indicated by the filter predicate T2.b2(+)=6, Q1 involves a filter predicate on the right table T2. Since Q1 involves a left outer join, the result of this query will include all results from table T1, regardless of whether they are joined to any rows from table T2. Since table T2 is the include matching table for the join operation, the rows in table T2 that do not match at least one row from table T1 are omitted from the result set of Q1. The database management system runs Q1.

[0073] When the database management system executes a query, it involves steps 102A and 102B. Specifically, in step 102A, after applying the filter to the materialized view, the query is rewritten to generate a rewritten query that retrieves data from the materialized view. For example, the database 232 stores the materialized view "MV1" with the following definition:

[0074] Create materialized view MV1…AS

[0075] SELECT T1.rowid T1RID,T1.b1,T2.b2,T2.c2,SUM(T1.d1)sm,COUNT(T1.d1)cnt,NVL2(T2.rowid,1,0)IVAL

[0076] FROM T1,T2

[0077] WHERE T1.a1=T2.a2(+)

[0078] GROUP BY T1.rowid,T1.b1,T2.b2,T2.c2,NVL2(T2.rowid,1,0);

[0079] Regarding the execution of Q1, the rewrite module of the database server instance 222 rewrites Q1 to retrieve data from MV1, which is based on the same join predicate (T1.a1 = T2.a2(+)) as Q1. According to the embodiment, MV1 is more general than Q1, that is, MV1 does not include the filter predicate included in Q1 (in this case, T2.b2(+) = 6 applied to the right table). Nevertheless, the database management system determines that MV has sufficient data for Q1 based on the matching join predicate.

[0080] In step 102B, the rewritten query is executed. For example, the database management system executes the rewritten query (step 102A).

[0081] Query rewrite using one-sided outer joins of many-to-many or one-to-many types

[0082] Figure 3Depict flowchart 300 for executing a rewritten query according to an embodiment, the rewritten query utilizing a materialized view and involving a one-sided join operation of many-to-many type or one-to-many type. Specifically, after the filter predicate from the original query is applied to the result of the join operation, the resulting set of rows should be integrated as detailed above. However, the result of a join operation that is a many-to-many type join or a one-to-many type join may include one or more rows having data that matches data from the included match table that does not satisfy the filter predicate in the query from all tables. Therefore, after applying the filter predicate to the data from the included match table of the one-sided join, the resulting set of rows may be unintegrated.

[0083] For illustration, Figure 4 Depict example content 400 of MV1, having rows 402 - 436 and columns 402 - 414, where the join operation is a one-sided outer join of many-to-many type. In MV1 content 400, row 420 includes a match between (a) a row in the left table T1 with row identifier (RID) "AB" and (b) a row in the right table T2 with B2 value "7". Thus, row 420 is an example of a result row of a join operation that includes data from the included match table that does not satisfy the filter predicate (T2.b2(+) = 6) in Q1. Additionally, row 422 of MV1 content 400 includes a match between (a) the same row in table T1 with RID "AB" and (b) a second row in the right table T2 with B2 value "6". Thus, row 422 is an example of a result row of a one-sided outer join operation that includes data from the included match table that satisfies the filter predicate (T2.b2(+) = 6) in Q1.

[0084] After applying the filter predicate from Q1 to content 400, row 420 is no longer an inner join type row, while row 422 continues to be an inner join type row. Since the query result includes an inner join type row 422 having the same data as row 402 from table T1, the result is unintegrated. By removing row 420 from the query result, the result is partially integrated.

[0085] In flowchart 300, the steps apply to each row in the materialized view. Flowchart 300 is described below using example query Q1 and also example content 400 of MV1. Note that due to null values present in the corresponding column of table T2, MV1 content 400 includes a null value in column C2 in row 422. Additionally, anti-join type rows 424, 425, and 435 have null values for data from table T2 because they do not join with any rows from that table. As Figure 4 shown, MV1 includes T1 unique to table T1 RIDcolumns, but also includes the IVAL column. According to an embodiment, any column unique in the table from among those including all tables can be used in the MV, and the embodiment is not limited to using row identifiers for uniquely including all table values. T1 in MV1 RID The columns and the IVAL column facilitate integrating the query result after applying the filtering predicate in Q1 to the MV data.

[0086] If there are more than two tables in the MV definition, the unique columns of each table (regardless of whether it is an all-inclusive table, with one exception) participating in a many-to-many or one-to-many join are included in the MV definition.

[0087] Many-to-many or one-to-many: First stage

[0088] According to an embodiment, for a query having the same unilateral outer join predicate as MV1, before processing the intermediate result from MV1, as specified in the respective first stages of the multi-stage technique for rewriting the query used for the above-described many-to-many and many-to-one types of outer joins, a database management system (such as Figure 2 database management system 210) applies any filtering predicate from the query that is defined on the inner join tables or all-inclusive tables in the query. For illustration, the database management system processes the following query "Q1B", which is similar to Q1 but also includes a filtering predicate on T1.e (i.e., T1.e = 5).

[0089] (Q1B)

[0090] SELECT T1.b1,T2.c2,SUM(T1.d1),COUNT(T1.d1)

[0091] FROM T1,T2

[0092] WHERE T1.a1=T2.a2(+)and T2.b2(+)=6and T1.e=5

[0093] GROUP BY T1.b1,T2.c2;

[0094] Thus, Q1B includes a filtering predicate on the left (all-inclusive) table of the outer join expression. According to an embodiment, the database management system applies the filtering predicate to table T1 before performing Figure 3 the steps of flowchart 300 in

[0095] Many-to-many or one-to-many: Second stage and third stage

[0096] In step 302, determine whether the corresponding indicator value in the indicator column of the materialized view included in the corresponding row indicates that the corresponding row is an inner join type row. For example, the database management system determines whether the row is associated with an IVal from the IVAL column indicating that a specific row (such as Figure 4 row 420 in) is an inner join type row. For row 420, it is associated with an IVal of "1" in MV1, so it is an inner join type row.

[0097] In step 304, based on determining that the corresponding indicator value indicates that the corresponding row is an inner join type row: determine whether the corresponding row meets the filter from the original query. For example, the database management system determines whether the inner join type row 420 meets the filter in Q1, which is T2.b2(+) = 6. Row 420 has a value of "7" in the B2 column (which represents the value from T2.b2), so it does not meet the filtering predicate from Q1.

[0098] Rows of the modified anti-join type (MAJ)

[0099] In step 306, based on determining that the corresponding row does not meet the filter: perform one or more modified anti-join actions, which include changing the corresponding indicator value in the intermediate result set to reflect that the corresponding row is a modified anti-join row. For example, in response to determining that row 420 has an IVal of "1" and does not meet the filtering predicate, the database management system adjusts the IVal for row 420 to "-1" to indicate that the row is a row of the modified anti-join type (MAJ). MAJ rows are initially inner join type rows in the join operation result set, but become anti-join type rows after applying the filtering predicate to the join operation result set.

[0100] After changing one or more inner join type rows to MAJ rows in the intermediate result set of the query, there are four MAJ scenarios (A)-(D) for each row in the intermediate result of the query that includes all the table(s):

[0101] A. It has zero MAJ rows and one or more inner join type rows.

[0102] B. It has zero MAJ rows and one anti-join type row.

[0103] C. It has one or more MAJ rows and one or more inner join type rows.

[0104] D. It has one or more MAJ rows and zero inner join type rows.

[0105] According to an embodiment, the MAJ row indicators in the intermediate result set are used to integrate the result set of a query. For the case of including all table rows in scenario (A) or (B), such a set of rows corresponding to including all rows in all tables has no MAJ rows (i.e., has already been integrated), and thus no additional processing is required. For the case of including all table rows in scenario (C), all MAJ rows should be discarded from the set of rows corresponding to including all rows in all tables in the result set, because no anti-join type rows should appear together with the inner-join type rows in the integrated result set. For the case of including all table rows in scenario (D), all but one of the MAJ rows should be discarded from the set of rows corresponding to including all rows in all tables in the result set, because each eligible row including all tables must be retained.

[0106] For illustration, based on MV1, the following query "Q1R" is an example rewrite of Q1:

[0107]

[0108] Specifically, Q1R is an example embodiment of a query rewrite for a many-to-many outer join of Q1 in SQL, which, as described above, includes a predicate on the right table T2.

[0109] The example rewritten query Q1R uses the IVal values for the rows in MV1 to integrate the intermediate result set of Q1. At lines 4-7, Q1R generates a first set of intermediate results from MV1. At line 6 of Q1R, for a given row in the intermediate result set, the IVal of that row is set to -1, unless the row is an inner-join type row (IVAL = 1) that satisfies the filter (b2 = 6) on the right table, or the row is an anti-join type row (IVAL = 0). Thus, a row that is an inner-join type row not satisfying the filter on the right table is marked as a MAJ row with an IVal of "-1".

[0110] According to an embodiment, the third row of Q1R uses the LEAD function. The LEAD function in Q1R is an existing ANSI standard analytic function that returns the value of an expression from the next row or subsequent row in the table or partition to which the function is applied. The "partition by" clause is used to split the rows generated by lines 4 - 7 of the intermediate result into partitions based on unique values from table T1 (i.e., RID values). The "order by" clause sorts the data within each partition by Ival. Thus, the LEAD function allows specifying a value to return when there is no next or subsequent row for the current row in the partition (in Q1R, "2"). According to another embodiment, the existing ANSI standard analytic function LAG can be used here with the descending order specified in the ORDER BY clause. (Additional information on the LEAD and LAG functions can be found in Oracle Database Online Documentation, 10g Release 2 (10.2), pages 5 - 85 - 5 - 86 and pages 5 - 81 - 5 - 82 respectively.)

[0111] Since the IVal of the MAJ row is "-1", the MAJ IVal ranks at the extreme (first position or last position) of the list of all IVal (-1, 0, 1). This IVal selection allows the partition / sort operation performed by the LEAD function to mark exactly one MAJ row of the partition that contains only MAJ rows (marked with "2" in the MR column of the intermediate result).

[0112] For further illustration, Figure 5 depict the intermediate result set 500 that would be generated by the following query "QIR_INTERMEDIATE".

[0113]

[0114] Note that in the intermediate result set 500, the values in the CNT column are all "1", which reflects the small size of the example table in the example, rather than what the CNT would necessarily reflect if the table were larger.

[0115] The intermediate result set 500( Figure 5 ) illustrates the operation of Q1R. Specifically, the LEAD function marks the extreme rows in each partition with "2" in the MR column, i.e., the first row or the last row (in this example, the last row in each partition). Since the rows are sorted by IVal, the only time the MAJ row will be marked with "2" in the MR column is when there is only the MAJ row in the partition. For example, for the partition of T1 RID "AI", rows 533 - 535, include only MAJ rows, and only row 535 is marked with "2" in the MR column.

[0116] According to an embodiment, in response to determining that a given row from MV1 is an inner-join type row and the row does not satisfy the filter predicate from Q1, the database management system makes the columns from the matching table(s) among the columns of the row in the intermediate result set empty. For example, in the 5th row of Q1R, for a given row in the intermediate result set, the data from the right table T2 (i.e., c2) is made empty, unless the row is an inner-join type row (IVAL = 1) that satisfies the filter on the right table (b2 = 6). Thus, the data from table T2 that does not satisfy the filter is empty in the intermediate result set.

[0117] According to an embodiment, the database management system also makes one or more aggregate function values defined on one or more columns including the matching table(s) empty in the intermediate result of the MAJ rows.

[0118] Many-to-many or one-to-many: Fourth phase

[0119] Returning to the discussion of the example query rewrite Q1R, the 8th row of Q1R selects all rows from the intermediate result set where the IVal is "1" or "0" after applying the LEAD function, or the IVal is "-1" and the MR flag is "2". Thus, if the partition only includes MAJ rows, only one of these rows is selected to be included in the result set of Q1R. Additionally, all MAJ rows that appear together with one or more inner-join type rows with the same RID from table T1 in the intermediate result set are also removed from the query result set. In this way, the query result set returned for Q1R is consolidated.

[0120] Consider four groups of rows in the intermediate result set 500( Figure 5 ) related to the four MAJ scenarios listed above.

[0121] · In the group in T1 RID equal to "AA" (rows 520 - 521), there are no MAJ rows. This does not require special handling.

[0122] · In the group in T1 RID equal to "AJ" (row 536), there are no MAJ rows. This does not require special handling.

[0123] · In the group in T1 RID equal to "AB" that has both inner-join type rows and MAJ rows (rows 522 - 524), no MAJ rows have been marked with, for example, "2". Thus, all MAJ rows will be discarded because for the same left row, inner-join type rows and [modified] anti-join type rows cannot appear together.

[0124] · In T1 RIDIn the group with only MAJ rows equal to "AI" (lines 533 - 535), one of the three MAJ rows has been marked, for example, with "2". The other two unmarked MAJ rows will be discarded because a left row cannot appear more than once as a [modified] inner - joined type row.

[0125] Many - to - many or one - to - many: Fifth stage

[0126] After performing the steps represented in Q1R, the database management system performs filtering and joins with other tables not referenced in the MV definition if needed. For example, the database management system receives the following query "Q1C", which is similar to Q1 but also includes a filter on table T3.

[0127] (Q1C)

[0128] SELECT T1.b1,T2.c2,SUM(T1.d1),COUNT(T1.d1)

[0129] FROM T1,T2,T3

[0130] WHERE T1.a1=T2.a2(+)and T2.b2(+)=6and T1.b1=T3.d3 and T3.e=5

[0131] GROUP BY T1.b1,T2.c2;

[0132] The database management system uses MV1 to execute Q1C in a way similar to Q1 above. After filtering out all unmarked MAJ rows from the intermediate result, the database management system performs filtering on table T3 and joins with the intermediate result of Q1C based on the join predicates and filter predicates involving table T3 that are not included in the definition of the MV.

[0133] Many - to - many or one - to - many: Sixth stage

[0134] In addition, as needed, the database management system performs recalculation of grouping and aggregation on the grouping columns specified in the query. Specifically, if the grouped results stored in the MV are used to rewrite a query that returns fewer grouped items, recalculation of grouping and aggregation is required. This recalculation "rolls up" the finer - grained information in the MV into the coarser - grained information of the query request being rewritten. Thus, the grouping and aggregation results are recalculated after the completion of stage 5.

[0135] Query rewrite using one - sided outer joins of many - to - many type

[0136] In addition, the following presents an embodiment of a four-phase technique for rewriting a query using a many-to-one unilateral outer join based on an MV (as indicated above).

[0137] 1. Apply the filter predicates defined in the query on the all tables and inner join tables to the MV filter.

[0138] 2. For each row of the MV and for each unilateral outer join in the MV: If it is an inner join row and the row does not satisfy the filter predicates specified in the query on the included match table, then (A) nullify the columns from the included match table, and (B) nullify the aggregate functions defined on the columns of the included match table.

[0139] 3. Perform the filtering and join with other tables not referenced in the MV definition, if needed.

[0140] 4. Recalculate the grouping and aggregation on the grouping columns specified in the query.

[0141] According to an embodiment, if the query being rewritten specifies a many-to-one unilateral outer join, it is not necessary to include the unique columns of the all tables in the definition of the MV used to rewrite the query. For example, when the left outer join predicate is equality and the right table column in the outer join predicate is unique, it is not necessary to include the unique columns of the left table in the MV definition because each left row will match at most one row from the right table. Thus, each row from the left table will be represented exactly once in the query result.

[0142] Figure 6 FIG. 600 depicts a flow chart according to an embodiment for executing a rewritten query that utilizes a materialized view and involves a many-to-one type of unilateral outer join operation. In flow chart 600, the steps apply to each row in the materialized view. The following example query "Q2" is used to describe flow chart 600. The query includes a left outer join predicate (F.a = D1.a(+)) with left table F and right table D1. Q2 further includes a filter predicate (D1.y(+) = 5) on the right table D1. Note that considering that D1.a appearing in the join predicate is unique to table D1, the join predicate in Q2 defines a many-to-one unilateral outer join.

[0143] (Q2)

[0144] SELECT F.b,D1.x,SUM(F.m),COUNT(F.m)

[0145] FROM F,D1

[0146] WHERE F.a=D1.a(+)and D1.y(+)=5

[0147] GROUP BY F.b,D1.x;

[0148] In this embodiment, the database being managed by the database management system (such as database 232 being managed by database management system 210) stores a materialized view "MV2" with the following definition, where the definition of MV2 does not have a filter predicate and a many-to-one left outer join predicate that match the join predicate of Q2.

[0149] Create materialized view MV2…AS

[0150] SELECT F.b,D1.x,D1.y,SUM(F.m)sm,COUNT(F.m)cnt,

[0151] NVL2(D1.rowid,1,0)IVAL

[0152] FROM F,D1

[0153] WHERE F.a = D1.a(+)

[0154] GROUP BY F.b,D1.x,D1.y,NVL2(D1.rowid,1,0);

[0155] Figure 7 Depict an example content 700 of MV2. Note that MV2 includes anti-join type rows 723, 724, and 729, which have null values for the data from this table because they do not join with any rows from table T1. Additionally, since the join is many-to-one, each left table row matches at most one right table row. MV includes the IVAL column, but does not include any columns unique to the left table in this table because the embodiment does not require unique values from table F and the unique values from table F are not mentioned in Q2.

[0156] The database management system runs Q2 on the database. Regarding executing the example query Q2, the rewrite module of the database management system rewrites the query to retrieve data from MV2, which is based on the same join predicate as Q2 (F.a = D1.a(+)). According to the embodiment, MV2 is more general than Q2, that is, MV2 does not include the filter predicate included in Q2 (in this case, the filter predicate on the right table).

[0157] For illustration, the database management system rewrites Q2 as follows in the example rewritten query Q2R:

[0158]

[0159] As part of executing Q2 on the database, the database management system executes the rewritten Q2R on the database.

[0160] Many-to-one: First stage

[0161] According to an embodiment, for a query having the same join predicates as MV2, before processing the intermediate results from MV2, as specified in stage 1 of the multi-stage technique for rewriting queries for outer joins of the many-to-many and many-to-one types, the database management system applies any filter predicates from the query that are defined on the inner join tables in the query or on all tables (i.e., non-outer join tables). By way of illustration, the database management system receives the following query "Q2B", which is similar to Q2 but also includes a filter predicate on F.e (i.e., F.e = 7).

[0162] (Q2B)

[0163] SELECT F.b,D1.x,SUM(F.m),COUNT(F.m)

[0164] FROM F,D1

[0165] WHERE F.a = D1.a(+) and D1.y(+) = 5 and F.e = 7

[0166] GROUP BY F.b,D1.x;

[0167] Thus, Q2B includes a filter predicate on the left (including all) table of the outer join expression. According to an embodiment, the database management system applies the filter predicate to table F before performing the steps of flowchart 600.

[0168] Many-to-one: Second stage

[0169] Returning to the discussion of flowchart 600 for executing the rewritten query that utilizes a materialized view and involves a one-sided join operation of the many-to-one type, at step 602, it is determined whether the corresponding indicator value in the indicator column of the materialized view included in the corresponding row indicates that the corresponding row is an inner join type row. For example, the database management system determines whether a row is associated with an IVal from the IVAL column indicating that a particular row (such as row 722 of MV2 content 700) is an inner join type row. Since row 722 is associated with an IVal of "1", it is an inner join type row.

[0170] In step 604, based on determining that the corresponding indicator value indicates that the corresponding row is an inner join type row: determine whether the corresponding row satisfies the filter indicated in the original query. For example, the database management system determines whether the inner join type row 722 satisfies the filter D1.y(+) = 5 in Q2. Row 722 has the value "8" in the Y column (which represents the value from D1.y), so it does not satisfy the filter from Q1.

[0171] In step 606, based on determining that the corresponding row does not satisfy the filter, perform one or more of the following: make the one or more columns from the corresponding row that include the matching table null, and make the one or more aggregate functions defined for the one or more columns from the matching table null. For example, the database management system generates the intermediate results of Q2R, in which the data from D1 (such as D1.x) is null in the rows that do not match the filter on the data from the right table.

[0172] For illustration, Figure 7 depict the set of intermediate results 730 calculated by the following query "Q2R_B":

[0173] (Q2R_B)

[0174] SELECT b,x,y,sm,cnt,IVAL,

[0175] FROM(SELECT b,x,y,SM,CNT,IVAL

[0176] (CASE WHEN IVAL = 1 and y = 5 THEN y ELSE NULL END) AS y,

[0177] (CASE WHEN IVAL = 1 and y = 5 THEN x ELSE NULL END) AS x

[0178] FROM MV2

[0179] WHERE e = 7);

[0180] The set of intermediate results 730 illustrates the operation of Q2R. Specifically, as Figure 7 depicted in the set of intermediate results 730, rows 722, 725, and 727 do not match the filtering criteria, so the data from the right table D1 (in columns 704 and 706) has been made null in these rows, effectively converting these rows into anti-join rows.

[0181] In addition, according to an embodiment, the database management system makes one or more aggregate functions defined on the columns of the right table null.

[0182] Many-to-One: Third Phase

[0183] As with the join operations of the many-to-many and one-to-many types, after the data from the right table is empty in the result of Q2, the database management system performs filtering and joins with other tables not referenced in the MV definition if needed. For example, the database management system receives the following query "Q2C", which is similar to Q2 but also includes a filter on table T3.

[0184] (Q2C)

[0185] SELECT F.b,D1.x,SUM(F.m),COUNT(F.m)

[0186] FROM F,D1,T3

[0187] WHERE F.a=D1.a(+)and D1.y(+)=5and F.b=T3.d and T3.e=5

[0188] GROUP BY F.b,D1.x;

[0189] The database management system uses MV2 to execute Q2C in a similar manner to Q2 above. After executing the second phase, the database management system performs filtering on table T3 and its join with the intermediate result of Q2C based on the filter predicates and join predicates involving table T3 that are not included in the definition of MV2.

[0190] Many-to-One: Fourth Phase

[0191] In addition, the database management system performs any necessary recalculation of grouping and aggregation on the grouping columns specified in the query. Specifically, if the grouped results stored in the MV are used to rewrite a query that returns fewer grouped items, a recalculation of grouping and aggregation is required. This recalculation "rolls up" the fine-grained information in the MV into the coarse-grained information of the query request being rewritten. Thus, any affected grouping and aggregation results are recalculated after the third phase is completed.

[0192] Additional Example

[0193] The following definition for "MV3" has no filter predicates and no many-to-many left outer join predicates, many-to-many inner join predicates, or many-to-one inner join predicates. In MV3, the join column D3.c is unique to table D3. The MV definition further includes unique columns from tables F and D2.

[0194] Create materialized view MV3…AS

[0195] SELECT F.rowid F_RID,F.d,F.p,D1.x,D2.rowid D2_RID,D2.z,D1.y,D3.z,D2.s,SUM(F.m)sm,COUNT(F.m)cnt,NVL2(D1.rowid,1,0)IVAL

[0196] FROM F,D1,D2,D3

[0197] WHERE F.a = D1.a(+) and F.b = D2.b and F.c = D3.c

[0198] GROUP BY F.rowid,F.d,F.p,D1.x,D2.rowid,D2.z,D2.s,D1.y,D3.z,

[0199] NVL2(D1.rowid,1,0);

[0200] The following query "Q3" has many-to-many left outer joins, many-to-many inner joins, and many-to-one inner joins.

[0201] (Q3)

[0202] SELECT F.d,D1.x,D2.z,SUM(F.m),COUNT(F.m)

[0203] FROM F,D1,D2,D3,D4

[0204] WHERE F.a = D1.a(+) and F.b = D2.b and F.c = D3.c and D1.y(+) = 7 and

[0205] F.p = D4.p and F.d > 2;

[0206] GROUP BY F.d,D1.x,D2.z;

[0207] To rewrite Q3 using MV3, according to the embodiment, the database management system generates the following query "Q3R", where the "partition by" clause of the LEAD function contains the RIDs of tables F and D2. Additionally, MV3 is filtered with the predicate "d > 2" and joined with table D4.

[0208]

[0209] The following MV definition of "MV4" has no filter predicates but has two many-to-many left outer joins. Since there are two left outer joins in the MV definition, there are two IVal columns (IVAL1 and IVAL2) in the MV, one for each right table. Additionally, since there are more than two tables in the definition of MV4 and there is also a unilateral outer join in the MV definition, the unique columns of all three tables (F, D1, and D2) are included in the MV definition (because both left outer joins can duplicate the rows of the left table F), and these columns are used to integrate the query results as shown below.

[0210] Create materialized view MV4…AS

[0211] SELECT F.rowid F_RID,D1.rowid D1_RID,D2.rowid D2_RID,F.d,D1.x,D1.y,D2.s,D2.z,SUM(F.m)sm,COUNT(F.m)cnt,

[0212] NVL2(D1.rowid,1,0)IVAL1,NVL2(D2.rowid,1,0)IVAL2

[0213] FROM F,D1,D2

[0214] WHERE F.a=D1.a(+)and F.b=D2.b(+)

[0215] GROUP BY F.rowid,D1.rowid,D2.rowid,F.d,D1.x,D1.y,D2.s,D2.z,

[0216] NVL2(D1.rowid,1,0),NVL2(D2.rowid,1,0);

[0217] The following query "Q4" has two left outer join predicates that match the join predicates from MV4 and also has filter predicates on all three tables (F, D1, and D2).

[0218] (Q4)

[0219] SELECT F.d,D1.x,D2.z,SUM(F.m),COUNT(F.m)

[0220] FROM F,D1,D2

[0221] WHERE F.a=D1.a(+)and F.b=D2.b(+)and D1.y(+)=5and D2.s(+)=7

[0222] and F.d = 11

[0223] GROUP BY F.d, D1.x, D2.z;

[0224] According to an embodiment, the database management system rewrites Q4 into the following query "Q4R" based on MV4. Q4R includes two partition-by clauses, where each partition-by clause (a) corresponds to the left outer join in Q4, and (b) contains the unique columns of the other two tables.

[0225]

[0226] To rewrite Q4, considering the left outer join between F and D1, F can be regarded as already being outer joined with D2. Therefore, the unique key of the left table is considered to be the composite key (F_RID, D2_RID). Similarly, considering the left outer join between F and D2, F can be regarded as already being outer joined with D1. Therefore, the unique key of the left table is considered to be the composite key (F_RID, D1_RID). This is the underlying reason for partitioning on the composite unique keys for each left outer join in Q4R.

[0227] The following example query "Q5" has inner join operations and left outer join operations that match the join predicates of MV4 and the filter predicates on all three tables (F, D1, and D2).

[0228] (Q5)

[0229] SELECT F.d, D1.x, D2.z, SUM(F.m), COUNT(F.m)

[0230] FROM F, D1, D2

[0231] WHERE F.a = D1.a and D1.y = 4 and F.b = D2.b(+) and D2.s(+) = 8

[0232] and F.d = 16

[0233] GROUP BY F.d, D1.x, D2.z;

[0234] According to an embodiment, the database management system rewrites Q5 into the following query "Q5R" based on MV4, where there is also an outer-to-inner join derivation.

[0235]

[0236] Application of other filter predicates

[0237] According to one or more embodiments, one or more filter predicates included in a query to be rewritten may also be applied to the entire tables or the inner join result set included in a join operation. According to an embodiment, before applying the filter predicates defined on one or more included matching tables of a query and / or before integrating query results, the filter predicates defined on the entire tables included in the inner join result set or the original query are applied to the MV data. This is specified in the respective first stage of a multi-stage technique for rewriting queries for the above-described many-to-many and many-to-one types of outer joins.

[0238] Unified filter predicate

[0239] A "unified" filter predicate is (a) a predicate that matches an expression with multiple values, or (b) a filter predicate that contains multiple filter predicates. For example, a given type of query is generally issued with a predicate that compares a specific expression with one of a specific set of literals (e.g., T2.b2 = 5; T2.b2 = 6; T2.b2 = 7; T2.b2 = 9; T2.b2 = 11). An MV with the following unified filter predicate T2.b2 IN (5, 6, 7, 9, 11) (which is semantically the same as "(T2.b2 = 5 OR T2.b2 = 6 OR T2.b2 = 7 OR T2.b2 = 9 OR T2.b2 = 11)") can be used to rewrite a given type of query with one of the filter predicates T2.b2 = 5, T2.b2 = 6, T2.b2 = 7, T2.b2 = 9, or T2.b2 = 11. However, of course, an MV may not be used to rewrite a given type of query with a filter predicate (e.g., T2.b2 = 17) whose literal value is not included in the MV.

[0240] One advantage of generating an MV with a unified filter predicate is that the size of the MV is relatively small and it can be used to rewrite some common queries. A disadvantage is that the unified filter predicate (which only applies to a specific set of literal values) limits the scope of the MV - the ability to be used to rewrite a wide range of queries. It is important to observe that the query rewriting techniques described herein still apply whether the MV does not contain a filter predicate or it contains a unified filter predicate.

[0241] Network layout architecture

[0242] Figure 2FIG. is a block diagram depicting an example network arrangement 200 for a database management system 210 in accordance with one or more embodiments. The network arrangement 200 includes a client device 240 and a database server computing device 220 communicatively coupled via a network 250. The network 250 may be implemented using any type of medium and / or mechanism that facilitates the exchange of information between the client device 240 and the server device 220. In accordance with one or more embodiments, the example network arrangement 200 may include other devices, including client devices, server devices, storage devices, and display devices.

[0243] The client device 240 may be implemented by any type of computing device communicatively connected to the network 250. In the network arrangement 200, the client device 240 is configured with a database client, which may be implemented in any number of ways, including being implemented as a stand-alone application running on the client device 240, or being implemented as a plug-in inserted into a browser running on the client device 240, and so on. Depending on the particular implementation, the client device 240 may be configured with other mechanisms, processes, and functionality.

[0244] In the network arrangement 200, the server device 220 is configured with a database server instance 222. The server device 220 is implemented by any type of computing device capable of communicating with the client device 240 via the network 250 and also capable of running the database server instance 222.

[0245] The database server instance 222 on the server device 220 maintains access to the data in the database 232 (i.e., on the storage device 230) and manages the data. In accordance with one or more embodiments, access to a given database includes access to (a) a set of disk drives storing the data for the database and (b) the data blocks stored thereon. The database 232 may reside in any type of storage device 230, including volatile and non-volatile storage devices such as random access memory (RAM), one or more hard disks, main memory, and so on.

[0246] In accordance with one or more embodiments, any of the functionality attributed to the database server instance 222 or the database management system 210 may herein be performed by any other entity that may or may not be depicted in the network arrangement 200. Depending on the particular implementation, the server device 220 may be configured with other mechanisms, processes, and functionality.

[0247] In an embodiment, each of the processes and / or functionality described in relation to database server instance 222, database management system 210, and / or database 232 is performed automatically and can be implemented in any general-purpose or special-purpose computer using one or more computer programs, other software elements, and / or digital logic while performing data retrieval, transformation, and storage operations that involve interacting with the memory of the computer and changing the physical state of the memory.

[0248] Database Overview

[0249] Embodiments of the present invention are used in the context of a database management system (DBMS). Accordingly, a description of an example DBMS, such as database management system 210, is provided.

[0250] Generally, a server, such as a database server, is an allocated combination of integrated software components and computing resources, such as memory, nodes, and processes on the nodes for executing the integrated software components, where the combination of the software and computing resources is dedicated to providing a specific type of functionality on behalf of clients of the server. The database server controls and facilitates access to a particular database and processes requests from clients to access the database.

[0251] A user interacts with the database server of a DBMS by submitting commands that cause the database server to perform operations on data stored in the database. The user can be one or more applications running on a client computer that is interacting with the database server. Multiple users may also be collectively referred to as users herein.

[0252] A database includes data and a database dictionary stored on a permanent storage mechanism, such as a set of hard disks. The database is defined by its own separate database dictionary. The database dictionary includes metadata that defines database objects contained in the database. In effect, the database dictionary defines the entire database. Database objects include tables, table columns, and table spaces. A table space is a collection of one or more files for storing data for various types of database objects, such as tables. If the data of a database object is stored in a table space, the database dictionary maps the database object to the one or more table spaces that hold the data of the database object.

[0253] The database dictionary is referenced by the DBMS to determine how to execute database commands submitted to the DBMS. Database commands can access database objects defined by the dictionary.

[0254] Database commands can be in the form of database statements. In order for a database server to process a database statement, the database statement must conform to the database language supported by the database server. A non-limiting example of a database language supported by many database servers is SQL, including proprietary forms of SQL supported by database servers such as Oracle (e.g., Oracle Database 11g). SQL data definition language (“DDL”) instructions are sent to the database server to create or configure database objects such as tables, views, or complex types. Data manipulation language (“DML”) instructions are sent to the DBMS to manage the data stored within the database structure. For example, SELECT, INSERT, UPDATE, and DELETE are common examples of DML instructions found in some SQL implementations. SQL / XML is a common extension of SQL used when manipulating object-managed XML data in the database.

[0255] A single-node database system (such as Figure 2 the system 210 depicted in

[0256] includes a single node that runs a database server instance that accesses and manages a database. According to an embodiment, the system 210 is implemented as a multi-node database management system, which consists of interconnected nodes that share access to the same database. Generally, the nodes are interconnected via a network and share access to a shared storage device to varying degrees, e.g., shared access to a set of hard disk drives and the data blocks stored thereon. The nodes in a multi-node database system can be in the form of a group of computers (e.g., workstations, personal computers) interconnected via a network. Alternatively, the nodes can be nodes of a grid, which consists of nodes in the form of server blades interconnected with other server blades on a rack.

[0257] Resources from multiple nodes in a multi-node database system can be allocated to the software that runs a particular database server. Each combination of the software and the allocated resources from the nodes is a server that is referred to herein as a “server instance” or “instance”. A database server can include multiple database instances, some or all of which are running on separate computers (including separate server blades).

[0258] Query processing

[0259] A query is an expression, command, or set of commands that, when executed, causes a server to perform one or more operations on a data collection. A query can specify the source data object(s), such as a table(s), column(s), view(s), or snapshot(s), from which to determine the result set(s). For example, the source data object(s) can appear in the FROM clause of a Structured Query Language (“SQL”) query. SQL is a well-known example language for querying database objects. As used herein, the term “query” is used to refer to any form that represents a query, including database statements and queries in the form of any data structure for internal query representation. The term “table” refers to any source object that is referenced or defined by a query and represents a set of rows, such as a database table, view, or inline query block (such as an inline view or subquery).

[0260] A query can perform operations on the data from the object(s) row by row as the source data object(s) are loaded, or on the entire source data object(s) after the object(s) have been loaded. The result set(s) produced by an operation (some operations) can be used by other operation(s), and in this way, the result set(s) can be filtered or narrowed based on some criteria, and / or joined or combined with other result set(s) and / or other source data object(s).

[0261] Generally, a query parser receives a query statement and produces an internal query representation of the query statement. Typically, the internal query representation is a set of interconnected data structures that represent the various components and structures of the query statement.

[0262] The internal query representation can be in the form of a node graph, with each interconnected data structure corresponding to a node and a component of the query statement being represented. The internal representation is typically produced in memory for evaluation, manipulation, and transformation.

[0263] Query Optimization

[0264] As used herein, a query is considered “rewritten” when the query (a) is rewritten from a first expression or representation to a second expression or representation (e.g., by using one or more materialized views), (b) is received in a manner that specifies or indicates a first set of operations (such as a first expression, representation, or execution plan) and is executed by using a second set of operations (such as the operations specified or indicated by a second expression, representation, or execution plan), or (c) is received in a manner that specifies or indicates a first set of operations and is planned to be executed by using a second set of operations.

[0265] When two queries or execution plans (if executed) would generate equivalent result sets, the two queries or execution plans are semantically equivalent to each other even if the result sets are assembled in different ways by the two queries or execution plans. If query execution generates a result set equivalent to the result set that the query or execution plan (if executed) would generate, then the execution of the query is semantically equivalent to the query or execution plan.

[0266] A query optimizer can optimize a query by rewriting the query. Generally, the query optimizer, or a rewrite module of a database server instance, rewrites the query into another query that generates the same result and may be executed more efficiently, i.e., a query for which it can produce an execution plan that may be more efficient and / or less costly. The query can be rewritten by manipulating any internal representation of the query (including any copy thereof) to form a rewritten query or a rewritten query representation. Alternatively and / or additionally, the query can be rewritten by producing different, but semantically equivalent, database statements.

[0267] Substitute or extend

[0268] According to one or more embodiments, one or more functions attributable to any process described herein according to one or more embodiments can be performed by any other logical or physical entity. In an embodiment, each of the techniques and / or functionality described herein is performed automatically and can be implemented using one or more computer programs, other software elements, and / or digital logic in any general-purpose or special-purpose computer while performing data retrieval, transformation, and storage operations that involve interacting with the memory of the computer and transforming the physical state of the memory.

[0269] Hardware Overview

[0270] According to one embodiment, the techniques described herein are implemented by one or more special-purpose computing devices. The special-purpose computing device can be hard-wired to perform the techniques, or can include digital electronic devices permanently programmed to perform the techniques, such as one or more application-specific integrated circuits (ASICs) or field-programmable gate arrays (FPGAs), or can include one or more general-purpose hardware processors programmed to perform the techniques according to program instructions in firmware, memory, other storage devices, or a combination. Such special-purpose computing devices can also combine custom hard-wired logic, ASICs, or FPGAs with custom programming for implementing the techniques. The special-purpose computing device can be a desktop computer system, a portable computer system, a handheld device, a networking device, or any other device incorporating hard-wired and / or program logic for implementing the techniques.

[0271] For example, Figure 8FIG. 0 is a block diagram illustrating a computer system 800 in which embodiments of the present invention may be implemented. The computer system 800 includes a bus 802 or other communication mechanism for conveying information, and a hardware processor 804 coupled to the bus 802 for processing information. The hardware processor 804 may be, for example, a general-purpose microprocessor.

[0272] The computer system 800 also includes a main memory 806 coupled to the bus 802 for storing information and instructions to be executed by the processor 804, such as random access memory (RAM) or other dynamic storage device. The main memory 806 may also be used to store temporary variables or other intermediate information during execution of instructions to be executed by the processor 804. When such instructions are stored on a non-transitory storage medium accessible to the processor 804, the computer system 800 becomes a special-purpose machine customized to perform the operations specified in the instructions.

[0273] The computer system 800 further includes a read-only memory (ROM) 808 or other static storage device coupled to the bus 802 for storing static information and instructions for the processor 804. A storage device 810, such as a magnetic disk, optical disk, or solid state drive, is provided and coupled to the bus 802 for storing information and instructions.

[0274] The computer system 800 may be coupled via the bus 802 to a display 812, such as a cathode ray tube (CRT), for displaying information to a computer user. An input device 814, including alphanumeric keys and other keys, is coupled to the bus 802 for communicating information and command selections to the processor 804. Another type of user input device is a cursor control 816, such as a mouse, trackball, or cursor direction keys, for communicating direction information and command selections to the processor 804 and for controlling cursor movement on the display 812. This input device typically has two degrees of freedom in two axes (a first axis (e.g., x) and a second axis (e.g., y)), which allows the device to specify a position in a plane.

[0275] The computer system 800 may implement the techniques described herein using custom hardwired logic, one or more ASICs or FPGAs, firmware, and / or program logic that in combination with the computer system causes the computer system 800 to be a special-purpose machine or programs the computer system 800 as a special-purpose machine. According to one embodiment, the techniques herein are performed by the computer system 800 in response to one or more sequences of one or more instructions contained in the main memory 806 being executed by the processor 804. Such instructions may be read into the main memory 806 from another storage medium, such as the storage device 810. Execution of the instruction sequence contained in the main memory 806 causes the processor 804 to perform the processing steps described herein. In an alternative embodiment, hardwired circuitry may be used in place of or in combination with software instructions.

[0276] As used herein, the term "storage medium" refers to any non-transitory medium that stores data and / or instructions that cause a machine to operate in a particular fashion. Such storage media may include non-volatile media and / or volatile media. Non-volatile media includes, for example, optical discs, magnetic disks, or solid state drives, such as the storage device 810. Volatile media includes dynamic memory, such as the main memory 806. Common forms of storage media include, for example, floppy disks, flexible disks, hard disk drives, solid state drives, magnetic tape, or any other magnetic data storage medium, CD-ROM, any other optical data storage medium, any physical medium with patterns of holes, RAM, PROM, and EPROM, FLASH-EPROM, NVRAM, any other memory chip or cartridge.

[0277] A storage medium is different from a transmission medium but may be used in combination with a transmission medium. A transmission medium participates in transferring information between storage media. For example, a transmission medium includes coaxial cables, copper wire, and fiber optics, including the wires that comprise the bus 802. Transmission media can also take the form of acoustic or light waves, such as those generated during radio wave and infrared data communications.

[0278] Various forms of media can be involved in carrying one or more sequences of one or more instructions to the processor 804 for execution. For example, the instructions can initially be carried on a magnetic disk or solid state drive of a remote computer. The remote computer can load the instructions into its dynamic memory and send the instructions over a telephone line using a modem. A modem local to the computer system 800 can receive the data on the telephone line and convert the data into an infrared signal using an infrared transmitter. An infrared detector can receive the data carried in the infrared signal, and appropriate circuitry can place the data on the bus 802. The bus 802 carries the data to the main memory 806, from which the processor 804 retrieves and executes the instructions. The instructions received by the main memory 806 can optionally be stored on the storage device 810 before or after being executed by the processor 804.

[0279] The computer system 800 also includes a communication interface 818 coupled to the bus 802. The communication interface 818 provides two-way data communication coupled to a network link 820 that connects to a local network 822. For example, the communication interface 818 can be an Integrated Services Digital Network (ISDN) card, a cable modem, a satellite modem, or a modem that provides a data communication connection to a corresponding type of telephone line. As another example, the communication interface 818 can be a local area network (LAN) card to provide a data communication connection to a compatible LAN. A wireless link can also be implemented. In any such implementation, the communication interface 818 sends and receives electrical, electromagnetic, or optical signals that carry digital data streams representing various types of information.

[0280] The network link 820 typically provides data communication with other data devices through one or more networks. For example, the network link 820 can provide a connection to a host 824 or a data device operated by an Internet service provider (ISP) 826 through the local network 822. The ISP 826 in turn provides data communication services through the global packet data communication network (now commonly referred to as the “Internet”) 828. Both the local network 822 and the Internet 828 use electrical, electromagnetic, and optical signals that carry digital data streams. Signals through various networks and on the network link 820 and through the communication interface 818 that carry digital data to and from the computer system 800 are example forms of transmission media.

[0281] The computer system 800 can send messages and receive data, including program code, through the network(s), the network link 820, and the communication interface 818. In the Internet example, the server 830 can send code for an application request through the Internet 828, the ISP 826, the local network 822, and the communication interface 818.

[0282] The received code can be executed by the processor 804 when it is received, and / or stored in the storage device 810 or other non-volatile storage means for later execution.

[0283] Software Overview

[0284] Figure 9 is a block diagram of a basic software system 900 that can be used to control the operation of the computer system 800. The software system 900 and its components (including their connections, relationships, and functions) are only intended to be exemplary and are not intended to limit the implementation of the example embodiment(s). Other software systems suitable for implementing the example embodiment(s) may have different components, including components with different connections, relationships, and functions.

[0285] The software system 900 is provided to direct the operation of the computer system 800. The software system 900, which can be stored in the system memory (RAM) 806 and on a fixed storage device (e.g., hard disk or flash memory) 810, includes a kernel or operating system (OS) 910.

[0286] The OS 910 manages the low-level aspects of computer operation, including managing the execution of processes, memory allocation, file input and output (I / O), and device I / O. One or more application programs, represented as 902A, 902B, 902C... 902N, can be "loaded" (e.g., transferred from the fixed storage device 810 to the memory 806) for execution by the system 900. Applications or other software intended to be used on the computer system 800 can also be stored as a collection of downloadable computer-executable instructions, e.g., for downloading and installation from an Internet location (e.g., a web server, an app store, or other online service).

[0287] The software system 900 includes a graphical user interface (GUI) 915 for receiving user commands and data in a graphical (e.g., "point and click" or "touch gesture") manner. These inputs can then be acted upon by the system 900 according to instructions from the operating system 910 and / or the application(s) 902. The GUI 915 is also used to display the results of the operations from the OS 910 and the application(s) 902, whereupon the user can supply additional input or terminate the session (e.g., log out).

[0288] OS 910 can execute directly on the bare hardware 920 of computer system 800 (e.g., processor(s) 804). Alternatively, a hypervisor or virtual machine monitor (VMM) 930 can be interposed between the bare hardware 920 and the OS 910. In this configuration, the VMM 930 acts as a software “buffer,” or virtualization layer, between the OS 910 and the bare hardware 920 of computer system 800.

[0289] The VMM 930 instantiates and runs one or more virtual machine instances (“guest machines”). Each guest machine includes a “guest” operating system (such as OS 910) and one or more applications (such as application 902) designed to execute on the guest operating system. The VMM 930 provides a virtual operating platform for the guest operating system and manages the execution of the guest operating system.

[0290] In some cases, the VMM 930 can allow the guest operating system to run as if it were running directly on the bare hardware 920 of computer system 800. In these cases, the same version of the guest operating system configured to execute directly on the bare hardware 920 can also execute on the VMM 930 without modification or reconfiguration. In other words, in some cases, the VMM 930 can provide full hardware and CPU virtualization to the guest operating system.

[0291] In other cases, for efficiency, the guest operating system can be specifically designed or configured to execute on the VMM 930. In these cases, the guest operating system “is aware” that it is executing on the virtual machine monitor. In other words, in some cases, the VMM 930 can provide para-virtualization to the guest operating system.

[0292] Computer system processing includes hardware processor time quotas and (physical and / or virtual) memory quotas for storing instructions executed by the hardware processor, for storing data generated by executing instructions by the hardware processor, and / or for storing the hardware processor state (e.g., contents of registers) between hardware processor time quotas when the computer system processing is not running. Computer system processing runs under the control of an operating system and can run under the control of other programs executing on the computer system.

[0293] The basic computer hardware and software described above are presented for the purpose of illustrating the basic underlying computer components that can be used to implement the example embodiment(s). However, the example embodiment(s) are not necessarily limited to any particular computing environment or computing device configuration. Instead, the example embodiment(s) can be implemented in any type of system architecture or processing environment that those skilled in the art, in view of the present disclosure, will understand to be capable of supporting the features and functionality of the example embodiment(s) presented herein.

[0294] Cloud computing

[0295] The term "cloud computing" is generally used herein to describe a computing model that enables on-demand access to a shared pool of computing resources such as computer networks, servers, software applications, and services, and enables the rapid provisioning and release of resources with minimal management effort or service provider interaction.

[0296] Cloud computing environments (sometimes referred to as cloud environments or clouds) can be implemented in a variety of different ways to best suit different requirements. For example, in a public cloud environment, the underlying computing infrastructure is owned by an organization that makes its cloud services available to other organizations or the general public. In contrast, a private cloud environment is generally intended for use by or within a single organization only. A community cloud is intended to be shared by several organizations within a community; while a hybrid cloud includes two or more types of clouds (e.g., private, community, or public) bundled together through data and application portability.

[0297] Generally speaking, cloud computing models enable some of those responsibilities that might previously have been provided by an organization's own information technology department to instead be delivered as service layers within a cloud environment for consumption by consumers (either within or outside the organization, depending on the public / private nature of the cloud). Depending on the particular implementation, the precise definition of the components or features provided by or within each cloud service layer can vary, but common examples include: Software as a Service (SaaS), in which the consumer uses software applications running on cloud infrastructure, and the SaaS provider manages or controls the underlying cloud infrastructure and applications. Platform as a Service (PaaS), in which the consumer can use software programming languages and deployment tools supported by the PaaS provider to develop, deploy, and otherwise control their own applications, and the PaaS provider manages or controls other aspects of the cloud environment (i.e., everything below the runtime execution environment). Infrastructure as a Service (IaaS), in which the consumer can deploy and run arbitrary software applications and / or provision processing, storage, networking, and other basic computing resources, and the IaaS provider manages or controls the underlying physical cloud infrastructure (i.e., everything below the operating system layer). Database as a Service (DBaaS), in which the consumer uses a database server or a database management system running on cloud infrastructure, and the DbaaS provider manages or controls the underlying cloud infrastructure, applications, and servers (including one or more database servers).

[0298] In the foregoing specification, embodiments of the invention have been described with reference to numerous specific details that may vary between different implementations. The specification and drawings are thus to be regarded in an illustrative rather than a restrictive sense. The scope of the invention, and the sole and exclusive indication of what the applicant intends the scope of the invention to be, is the literal and equivalent scope of a set of claims published from this application, in the specific form in which such claims are published (including any subsequent amendments).

Claims

1. A computer - implemented method, comprising: Storing, by a database management system, a materialized view based on a materialized view definition, the materialized view definition including a unilateral outer - join operation on an all - tables table and a match - tables table using one or more join predicates; Wherein the materialized view includes all rows of the all - tables table; Wherein the materialized view includes one or more rows of the match - tables table that satisfy the one or more join predicates; After storing the materialized view, receiving, by the database management system, a query on multiple tables in a database managed by the database management system; Wherein the query includes: The unilateral outer - join operation on the all - tables table and the match - tables table; and A filter on the match - tables table that is not included in the materialized view definition; In response to determining that the query includes the unilateral outer - join operation: Rewriting the query to generate a rewritten query, the rewritten query: Generating an intermediate result set, where generating the intermediate result set includes applying the filter to the one or more rows included in the materialized view, and Retrieving data from the intermediate result set; and Executing the rewritten query, thereby: Applying the filter to the one or more rows included in the materialized view to generate the intermediate result set, and Generating a result set based on the data retrieved from the intermediate result set; where the method is executed by one or more computing devices.

2. The computer - implemented method according to claim 1, wherein: The materialized view includes specific multiple rows corresponding to a specific row of the all - tables table; Executing the rewritten query includes: For each row of the specific multiple rows, determining whether the row satisfies the filter; If all rows of the specific multiple rows do not satisfy the filter, retaining only one row of the specific multiple rows in the result set of the rewritten query; and If one or more rows of the specific multiple rows satisfy the filter, retaining the one or more rows of the specific multiple rows in the result set of the rewritten query.

3. The computer - implemented method according to claim 1, wherein: The materialized view includes multiple rows; The materialized view includes an indicator column storing indicator values that indicate one of (a) rows of an inner - join type, or (b) rows of an anti - join type; Executing the rewritten query includes, for each row of the multiple rows: Determining whether the indicator value in the indicator column included in the row indicates that the row is a row of an inner - join type; Based on determining that the indicator value indicates that the row is a row of an inner - join type: Determining whether the row satisfies the filter; Based on determining that the row does not satisfy the filter: Performing one or more modified anti - join actions, the modified anti - join actions including changing the indicator value in the row corresponding to the row in the intermediate result set to reflect that the row is a row of a modified anti - join type.

4. The computer-executed method according to claim 3, wherein performing the one or more modified anti-join operations further comprises one or more of the following: making the one or more columns from the included match table in a particular row of the multiple rows empty; and making one or more aggregate function values defined for the one or more columns from the included match table empty.

5. The computer-executed method according to claim 3, wherein performing the rewritten query comprises: splitting the intermediate result set based on the unique columns of the included all tables to generate multiple partitions of the intermediate result set; performing one or more marking operations by, for each partition of the multiple partitions, if each partition includes only rows of the modified anti-join type, marking a single row of each partition; and filtering the unmarked rows of the modified anti-join type from the intermediate result set.

6. The computer-executed method according to claim 5, wherein: the indicator values in the indicator column are sorted such that the indicator values of the modified anti-join type are sorted to the extreme of the list of indicator values, the list of indicator values including the indicator values of the modified anti-join type, the indicator values of the inner-join type, and the indicator values of the anti-join type; the method further comprises: sorting the rows in each partition of the multiple partitions based on the indicator values of the rows in each partition such that the indicator values of the modified anti-join type are sorted to the extreme of each partition, wherein the one or more marking operations further comprise using an analysis function to mark the extreme rows of each partition of the multiple partitions.

7. The computer-executed method according to claim 3, wherein the indicator column is an inner-join indicator column.

8. The computer-executed method according to claim 1, wherein: the materialized view includes multiple rows; the materialized view includes an indicator column storing indicator values that indicate one of (a) rows of the inner-join type, or (b) rows of the anti-join type; performing the rewritten query comprises, for each row of the multiple rows: determining whether the indicator value in the indicator column included in each row indicates that each row is a row of the inner-join type; based on determining that the indicator value included in each row indicates that each row is a row of the inner-join type: determining whether each row satisfies the filter; based on determining that each row does not satisfy the filter, performing one or more of the following: making the one or more columns from the included match table in each row empty, and making one or more aggregate functions defined for the one or more columns from the included match table empty.

9. The computer-executed method according to claim 1, wherein the query further comprises a second filter on the included all tables.

10. The computer-executed method according to claim 1, wherein the query further comprises one or more additional included all tables.

11. The computer-executed method according to claim 1, wherein the query further comprises one or more additional included match tables.

12. One or more non-transitory computer-readable media storing instructions that, when executed by one or more processors, cause performance of the method according to any one of claims 1-11.

13. An apparatus comprising means for performing the method according to any one of claims 1-11.

14. An apparatus comprising: One or more processors; And A memory coupled to the one or more processors and including instructions stored thereon that, when executed by the one or more processors, cause performance of the method according to any one of claims 1-11.

15. A computer program product comprising instructions that, when executed by one or more processors, cause performance of the method according to any one of claims 1-11.

Images