Automatic generation of materialized views

Abstract

Automatically generating definitions of materialized views. Generally, automated MV generation identifies a set of candidate MVs by examining the working set of query blocks. Once candidates are formed, the candidate MVs are further evaluated to compute the benefit to the candidate MVs. An improved method for generating a candidate set of MVs is described herein. The improved method is referred to as the extended covered sub-expression technique (ECSE). Under ECSE, various relationships between join sets other than strict equivalence are used to generate new result join sets. Such relationships include subsets, intersections, supersets, and unions, which are described in further detail below. In some cases, relationships between the result join sets and the initial join sets are considered to generate new result join sets. The final result join sets are then used to form a candidate set of MVs.

Description

TECHNICAL FIELD

[0001] The present invention relates to the field of database management, and in particular to the automatic generation of materialized views (MV). BACKGROUND

[0002] In a database management system (DBMS), materialized views are an important feature of database performance. When retrieving a relatively small amount of data from a very large table, the optimal mix of materialized views maximizes resource utilization (CPU and IO) and improves application throughput.

[0003] Because of the importance of materialized views, identifying the optimal mix of materialized views is an important task. In general, identifying the optimal mix of materialized views includes identifying a small number of materialized views that have a reasonable size, contain a large amount of pre-computation of joins and groupings, and can rewrite a large number of workload queries.

[0004] Identifying materialized views can be performed manually. However, manually identifying materialized views requires a broad understanding of the structure of the database, the workload of queries, and the internal operations of the DBMS. Developers need to spend a significant amount of time and effort to manually identify materialized views, identify materialized views to create, establish indexes for materialized views, update them, and suggest which materialized views to use for their users.

[0005] Described herein are techniques for automatically generating materialized views. The methods described in this section are methods that can be employed, but are not necessarily methods that have previously been conceived or employed. Thus, unless otherwise indicated, they should not be assumed to be prior art merely because they are included in this section. SUMMARY

[0006] One example aspect of the present disclosure provides a method for auto-generation of materialized views, comprising: deriving a plurality of join sets from a set of query workloads, wherein each join set of the plurality of join sets represents a respective join graph for a respective set of query blocks from the set of query workloads, the respective join graph comprising one or more join edges, the one or more join edges each comprising one or more join conditions; wherein the constituent join sets and associated query block sets include a first join set and a first set of query blocks associated with the first join set, and a second join set and a second set of query blocks associated with the second join set, wherein the first join set references a table not referenced by the second join set or the second join set references a table not referenced by the first join set; applying a join set operation to the constituent join sets of the plurality of join sets and the associated query block sets to generate a result join set associated with a respective result query block set, the respective result query block set comprising each respective query block set of each constituent join set of the constituent join sets of the plurality of join sets, wherein a join graph of the respective result query block set is not strictly equivalent to a respective join graph of at least one of the constituent join sets; determining, based on the result join set associated with the respective result query block set, that a materialized view based on the result query block set provides a net workload benefit to a set of queries; responsive to determining that the materialized view provides the net workload benefit to the set of queries, selecting the materialized view for creation.

[0007] The present disclosure also includes other aspects and examples. BRIEF DESCRIPTION OF DRAWINGS

[0008] In the drawings of certain embodiments, like reference designations refer to corresponding parts throughout the several views:

[0009] Figure 1 An example join graph for a given query block is depicted.

[0010] Figure 2 is a graph illustrating invariant joins.

[0011] Figure 3 is a graph illustrating variant joins.

[0012] Figure 4 is a flowchart depicting a process for auto-generating materialized views from a list of query blocks using extended cover join subexpression technique.

[0013] Figure 4A is a flowchart depicting a process for auto-generating materialized views from a list of query blocks using cover join subexpression technique.

[0014] Figure 4B is a flowchart depicting a process for generating a subset or superset of join sets.

[0015] Figure 4C and Figure 4DAn example of the ECSE algorithm is provided.

[0016] Figure 5A An example flowchart for performing heuristic A is illustrated.

[0017] Figure 5B An example flowchart for performing heuristic B is illustrated.

[0018] Figure 5C An example flowchart for performing heuristic C is illustrated.

[0019] Figure 5D An example flowchart for performing heuristic D is illustrated.

[0020] Figure 5E An example flowchart for performing heuristic E is illustrated.

[0021] Figure 5F An example flowchart for performing heuristic F is illustrated.

[0022] Figure 6 is a flowchart depicting the process for the optimal global greedy algorithm.

[0023] Figure 7 is a block diagram of a computer system upon which embodiments of the application can be implemented.

[0024] Figure 8 is a block diagram of a software system which can be employed to control the operation of the computer system upon which embodiments of the application can be implemented. DETAILED DESCRIPTION

[0025] In the following description, for the purposes of explanation, numerous specific details are set forth in order to provide a thorough understanding of the present application. It will be apparent, however, that the present application can be practiced without

[0026] OVERALL SUMMARY

[0027] Techniques for the automatic generation of materialized views are described herein. Such techniques are referred to herein as automatic MV generation.

[0028] Generally, automatic MV generation identifies a set of candidate MVs by examining the working set of a query block. Once candidates are formed, the candidate MVs are further evaluated to compute the benefit to the candidate MVs.

[0029] The computation of the benefit takes into account factors such as the storage cost and maintenance cost of the MV. Another important factor to consider is the query execution cost savings realized by performing the MV rewrite for one or more queries in the workload. Moreover, the more queries for which the MV rewrite results in query execution cost savings, the more benefit the MV provides.

[0030] One method for generating a candidate MV set is referred to herein as the Covering Sub-Expression technique (CSE). In CSE, the relationship between fully equivalent join graphs is used to generate a new result join set. The result join set is then used to form a candidate MV set. For example, when a pair of constituent join sets in the workload and their corresponding pair of constituent query block sets are equivalent in composition, an equivalent join set operation can be applied to the pair of constituent join sets and their corresponding pair of constituent query block sets. The result join set representing the MV can be used to rewrite both constituent query block sets. In this case, the join graph of the result join set is equivalent to the respective join graph of each constituent join set.

[0031] An improved method for generating a candidate MV set is described herein. The improved method is referred to as the Extended Covering Sub-Expression technique (ECSE). Under ECSE, various relationships between join sets are used to generate a new result join set in addition to strict equivalence. Such relationships include subset, intersection, superset, and union, which are described in further detail below. In some cases, the relationship between the result join set and the initial join sets is considered to generate a new result join set. The final result join set is then used to form a candidate MV set. For example, when a pair of constituent join sets in the workload and a corresponding pair of constituent query block sets intersect in composition, an intersecting join set operation can be applied to the pair of constituent join sets and the corresponding pair of constituent query block sets. The result join set representing the MV can be used to rewrite both constituent query block sets. Here, the result join set is not equivalent to either constituent join set.

[0032] Key Definitions and Concepts

[0033] Query Block

[0034] A query block is a basic unit of a structured query language (SQL) statement that specifies a projection operation (e.g., columns specified in a SELECT clause) on a table, inline view, or view referenced by a FROM clause and can specify additional operations on tables and views such as joins and grouping. Query blocks can be nested within query blocks.

[0035] Provided below is an example of a query block nested within another query block.

[0036] SELECT e.name, e.salary

[0037] FROM (SELECT *

[0038] FROM emp e

[0039] WHERE e.salary>5000)

[0040] In the above example, the query contains two query blocks. The query block that is nested as an inline view contains the SQL statement SELECT * FROM emp e WHERE e.salary>5000, which projects all the fields from the table emp e when the salary is greater than 5000. The main query block contains the SQL statement SELECT e.name, e.salary FROM ('the nested query block'), which projects the name and salary fields from the result of the nested query block.

[0041] Connection graph

[0042] A connection graph is a graph structure that represents all the joins specified in a query block. The connection graph includes edges between nodes, each node representing a table, each edge representing a join between the two tables to which the edge is connected and representing the characteristics of the join, such as a particular join type (e.g., inner, outer, etc.) and particular join conditions, which include one or more join predicates.

[0043] Examples of query blocks are provided below. Figure 1 The connection graph for a given query block is depicted. SELECT s.student_name, s.address, c.course_name

[0044] FROM student s, course c WHERE s.year = 2018 and c.department = 'MATHEMATICS' and s.course_id = c.course_id

[0045] Referring to Figure 1 , node 101 represents the table student "s" ("s" is an alias for the student table), which contains columns id, student_name, address, course_id, and year; node 102 represents the table course "c", which contains columns course_id, course_name, department, course_fee, and instructor. Edge 104 represents an inner join between the table student "s" and the table course "c" based on the join condition or join predicate s.course_id = c.course_id.

[0046] Join set

[0047] A joinset is an abstraction of a join graph based on one or more query blocks. A joinset is essentially a set of join edges. In a join graph, tables (or views) form vertices (or nodes). A join condition between two tables forms a join edge between two vertices. A join edge can include one or more join predicates. For example, consider the following query block.

[0048] SELECT*

[0049] FROM T1,T2,T3

[0050] WHERE T1.x=T2.y and T1.a>T2.b

[0051] The join graph for this query block has three vertices: T1, T2, and T3. There is only a single join edge between T1 and T2, which is represented by the set of join predicates: {T1.x=T2.y, T1.a>T2.b}.

[0052] If a joinset includes a single table, then an empty or dummy join edge between the same table is added to the joinset. A joinset allows set operations to be applied to its underlying join graph. A joinset is associated with a corresponding set of query blocks, which represents a set of query blocks that can potentially be rewritten in the form of an MV based on the joinset (i.e., the join graph of the joinset).

[0053] A joinset and its associated set of query blocks can be represented herein as [joinset, set of query blocks]. For example, [{F1, D2, D3, D6}, {Q5, Q7}] represents the joinset {F1, D2, D3, D5} and its associated set of query blocks {Q5, Q7}. The joinset {F1, D2, D3, D6} contains tables F1, D2, D3, and D6 and their join edges (but the join edges are not shown in the example); its associated set of query blocks contains query blocks Q5 and Q7, which can potentially be rewritten in the form of an MV based on the joinset {F1, D2, D3, D6}. The set of query blocks that can be rewritten based on the joinset using an MV can be referred to herein as being capable of being rewritten using the joinset.

[0054] Joinset operations

[0055] Under ECSEs, set operations are applied to constituent joinsets to form a result joinset; when applied to constituent joinsets, set operations take into account tables and join edges. A joinset operation uses set-based relationships between constituent joinsets to generate a result joinset such that all associated sets of query blocks of the constituent joinsets can be rewritten in the form of an MV based on the result joinset. The union of the associated sets of query blocks of the constituent joinsets is the set of query blocks associated with the result joinset.

[0056] Two constituent join sets and their associated query block sets are provided below. In this simple example, performing an equivalent join set operation to form a result join set representing a potential MV can be used to rewrite both query blocks using the potential MV.

[0057] Q1:

[0058] SELECT F.n, SUM(F.m), D1.m

[0059] FROM F, D1

[0060] WHERE F.f = D1.p and F.x = 6 and D1.y = 25

[0061] GROUP BY F.n, D1.m;

[0062] [{F, D1}, {Q1}]

[0063] Q2:

[0064] SELECT F.y, COUNT(F.m), D1.h

[0065] FROM F, D1

[0066] WHERE F.f = D1.p and F.x = 11 and D1.y = 33 GROUP BY F.y, D1.h;

[0067] [{F, D1}, {Q2}]

[0068] [{F, D1}, {Q1, Q2}]

[0069] Create materialized view MV1 as

[0070] SELECT F.n, SUM(F.m), COUNT(F.m), D1.m, F.y, D1.h, F.x, D1.y FROM F, D1

[0071] WHERE F.f = D1.p and F.x IN (6, 11) and D1.y IN (25, 33) GROUP BY F.n, D1.m, F.y, D1.h, F.x, D1.y;

[0072] In the above example, query block Ql contains the SQL statement SELECT F.n, SUM(F.m), Dl.m FROM F, Dl WHERE F.fk = Dl.p and F.x = 6 and Dl.y = 25 GROUP BY F.y, Dl.h. This SELECT statement projects columns F.n, SUM(F.m), and Dl.m from tables F and Dl when the conditions F.x = 6 and Dl.y = 25 are met. The GROUP BY statement groups the results based on columns F.n, Dl.m, and F.m. The join set {F, Dl} has a join graph involving tables F and Dl and can be used to rewrite the query block set {Ql}.

[0073] Similarly, query block Q2 contains the SQL statement SELECT F.y, COUNT(F.m), Dl.h FROM F, Dl WHERE F.f = Dl.p and F.x = 11 and Dl.y = 33 GROUP BY F.y, Dl.h. This SELECT statement projects columns F.y, COUNT(F.m), and Dl.h from tables F and Dl when the conditions F.x = 11 and Dl.y = 33 are met. The GROUP BY F.m statement groups the results based on columns F.y and Dl.h. The join set {F, Dl} has a join graph involving tables F and Dl and can be used to rewrite the query block set {Q2}.

[0074] Since the two join sets are identical, an equivalent join set operation can be used to form the result join set {F, Dl}. This result join set represents the potential MVl, which has a join graph involving tables F and Dl and can be used to rewrite both query blocks Ql and Q2. In the above example, the given join set operation uses the SELECT F.n, SUM(F.m), COUNT(F.m), Dl.m, F.y, Dl.h, Fx, Dl.y statement to merge the projected columns in query blocks Ql and Q2. The predicate F.x IN (6, 11) in MVl unifies the predicate F.x = 6 in query block Ql and the predicate F.x = 11 in query block Q2. Similarly, the predicate Dl.y IN (25, 33) in MVl unifies the predicate Dl.x = 25 in query block Ql and the predicate Dl.y = 33 in query block Q2. The GROUP BY F.m statement groups the results based on columns F.n, Dl.m, F.y, Dl.h, Fx, and Dl.y. The join set operation unifies the predicates and merges the projected columns in query blocks Ql and Q2. Thus, an MV based on the join set {F, Dl} can be used to rewrite both query blocks Ql and Q2.

[0075] Notation

[0076] For brevity, notation for inner joins and for left outer joins. indicates that T2 is a table for an outer join and T1 is on the left of this outer join. Joinset represents a join graph in one or more query blocks; it is defined in paragraph

[0038] and explained in more detail below.

[0077] The notation () is used for ordered lists, {} for sets, [] for composite structures, and |S| for the cardinality of a set S (i.e., the number of elements). The notation is used for the empty set, ∈ for set membership. A ∈ B means that A is an element of set B. The notation is used for the universal quantifier. denotes all elements of T.

[0078] The notation is used for proper subset. A proper subset is a subset that is not identical to the original set - it contains fewer join edges. denotes that B is a proper subset of A. The notation is used for set exclusion. denotes that B is neither a subset of A nor equal to A.

[0079] The notation = is used for equivalence. When joinset B is equivalent to joinset A, the relationship between joinsets B and A can be denoted as B = A or A = B.

[0080] The notation ∩ is used for intersection. The intersection of two joinsets A and B can be denoted as A ∩ B, which is the joinset containing all join edges of A that also belong to B, and vice versa, but nothing else.

[0081] The notation is used for superset. A superset of joinset A is another joinset that contains all join edges in joinset A, plus other join edges. When joinset B is a superset of joinset A, the relationship between joinsets B and A can be denoted as

[0082] The notation ∪ is used for union. The union of two joinsets A and B is another joinset that contains all join edges present in joinsets A and B. A ∪ B denotes the union of A and B.

[0083] The notation is used for subset. A subset of joinset A is another set that contains only join edges of joinset A, but can not contain all join edges of A. When joinset B is a subset of joinset A, the relationship between joinsets B and A can be denoted as

[0084] The symbol ∧ is used for AND (and) relations, and ∨ is used for OR (or) relations. A statement A Λ B is true if both A and B are true; it is false if either is false. A statement A ∨ B is true if either A or B is true; it is false if and only if both are false.

[0085] The column names pk and fk are used for columns that are foreign keys and primary keys, respectively. Thus, Tl.pk denotes the primary key of table Tl, and T2.fk denotes the foreign key of table T2.

[0086] Invariant join

[0087] In general, a join is invariant for X if it generates exactly one row for each row in X; that is, the join does not duplicate or eliminate any rows of X. Here X is either a table or a join set. The invariance of X with respect to T is denoted by Invariant(X, T). Whether a table is invariant in a join can be derived from the properties of the join edges involved in the join.

[0088] Table Tl is in an invariant join with respect to its join with table T2 if the following four conditions are met:

[0089] 1. The join is an equi-join with join condition Tl.fk = T2.pk, where Tl.fk is a foreign key that references the primary key T2.pk;

[0090] 2. The column Tl.fk has a non-null constraint;

[0091] 3. The query block does not contain any filter or subquery predicate on T2, or these predicates are not included in the result join set.

[0092] 4. T2 is invariant in joins with tables other than Tl (if any) specified in the query block, or the other tables are not included in the result join set.

[0093] As will be explained in more detail, the existence of an invariant join is used to generate a join set that is a superset or a union of the underlying join sets, thereby allowing the materialized view to contain more pre-computation.

[0094] Examples of invariant joins are provided below.

[0095] Q4:

[0096] SELECT F.a, Dl.a, D5.a

[0097] FROM F, Dl, D5

[0098] WHERE F.fkl = Dl.pkl and F.fk2 = D5.pkl

[0099] GROUP BY F.a, D1.a, D5.a;

[0100] [{F, D1, D5}, {Q4}]

[0101] Figure 2 The relationship between tables F, D1 and D5 in query block Q4 is depicted.

[0102] Column fk1 is a non-null foreign key in table F that references the primary key pk1 in table D1. Tables F and D1 are joined based on the condition F.fk1 = D1.pk1. This is an example of F participating in an invariant join with D1. Column fk2 is a non-null foreign key in table F that references the primary key pk1 in table D5. Tables F and D5 are joined based on the condition F.fk2 = D5.pk1. In addition, the invariant join F.fk1 = D1.pk1 guarantees that all rows of F are preserved without duplication in its join with table D1; similarly, the invariant join F.fk2 = D5.pk1 guarantees that all rows of F are preserved without duplication in its join with table D5.

[0103] In contrast, provided below is an example of a non-invariant join.

[0104] Q5:

[0105] SELECT F.a, F.b, D1.a

[0106] FROM F, D1

[0107] WHERE F.fk1 = D1.pk1

[0108] GROUP BY F.a, F.b, D1.a;

[0109] [{F, D1}, {Q5}]

[0110] Figure 3 The relationship between tables F and D1 in query block Q5, and the relationship between tables F, D1 and D5 in query block Q6 is depicted.

[0111] In query block Q5, foreign key F.fk1 does not have a non-null constraint, but the foreign key fk1 in table F references the primary key pk1 in table D1. This violates rule #2 in

[0058] . Therefore, table F is not invariant in its join with D1.

[0112] Equivalent

[0113] Two join sets are considered equivalent if their join graphs are equivalent. When join set A is equivalent to join set B, one of the join sets can be pruned.

[0114] When join set B is equivalent to join set A, the relationship between join sets B and A can be expressed as B = A.

[0115] Examples of equivalent join set operations that prune join sets are provided below.

[0116] Q7:

[0117] SELECT F.n, F.g, SUM(F.m1), D7.y, D2.z

[0118] FROM F, D7, D2

[0119] WHERE F.f1 = D7.p and F.f2 = D2.p and F.x IN(4, 6) and D7.c = 25 GROUP BY F.n, F.g, D7.y, D2.z;

[0120] [{F, D7, D2}, {Q7}]

[0121] Q8:

[0122] SELECT F.n, AVG(F.m2), D7.p, D2.y

[0123] FROM F, D7, D2

[0124] WHERE F.f1 = D7.k and F.f2 = D2.p and F.x = 9 and D7.c = 5

[0125] GROUP BY F.n, D7.p, D2.y;

[0126] [{F, D7, D2}, {Q8}]

[0127] [{F, D7, D2}, {Q7, Q8}]

[0128] Create materialized view MV2... as

[0129] SELECT F.n, F.g, D7.y, D2.z, D7.p, D2.y, F.x, D7.c, AVG(F.m2), SUM(F.m1) FROM F, D7, D2

[0130] WHERE F.f1 = D7.p and F.f2 = D2.p and F.x IN(4, 6, 9) and D7.c IN(5, 25) GROUP BY F.n, F.g, D7.y, D2.z, D7.p, D2.y, F.x, D7.c;

[0131] In the example above, the join set {F,D7,D2} is associated with the query block set {Q7} and has a join graph involving tables F, D7, and D2 and their join edges. Similarly, the join set {F,D7,D2} is associated with the query block set {Q8} and has a join graph involving tables F, D7, and D2 and their join edges. Since the join set {F,D7,D2} associated with query block Q7 and the join set {F,D7,D2} associated with query block Q8 contain the same set of tables and the exact same join predicates connecting these tables (i.e., their join graphs are identical), the equivalent set operation can be applied to prune one of the join sets, and the remaining join set can be used to rewrite query blocks Q7 and Q8 into the MV.

[0132] The equivalent join set operation involves unifying the filtering predicates and merging the selection and group-by lists, creating an MV2 that can be used to rewrite both query blocks Q7 and Q8. In the example above, the predicate F.xIN(4,6,9) in MV2 unifies the predicate FxIN(4,6) in query block Q7 and the predicate Fx=9 in query block Q8. Similarly, the predicate D7.cIN(5,25) in MV2 unifies the predicate D7.c=5 in query block Q7 and the predicate D7.c=25 in query block Q8. The equivalent join set operation also uses the statement SELECT Fn,Fg,D7.y,D2.z,D7.p,D2.y,Fx,D7.c,AVG(F.m2),SUM(F.m1),SUM(F.m1) to merge the projected columns in query blocks Q7 and Q8.

[0133] Intersection

[0134] The intersection of two connection sets A and B can be represented as A∩B, which is the connection set that includes all connection edges of A that also belong to B, and vice versa, but excludes other things.

[0135] In one embodiment, when the corresponding connection graphs intersect, an intersection operation can be applied to the connection set.

[0136] More specifically, when there are common edges between two constituent join sets, the intersection join set operation can be applied to generate a result join set and a result query block set associated with the result join set. The common edges between the two constituent join sets are established as the result join, and its associated query block set is the union of the query block sets of the constituent join sets.

[0137] Below is an example of an intersection join set operation that generates the result join set representing the MV.

[0138] Q9:

[0139] SELECT F.n, SUM(F.m1), D7.y, D2.z

[0140] FROM F, D7, D2

[0141] WHERE F.f1 = D7.p and F.f2 = D2.p and F.x IN(4,6) and D7.c = 25 GROUP BY F.n, D7.y, D2.z;

[0142] [{F, D7, D2}, {Q9}]

[0143] Q10:

[0144] SELECT F.y, COUNT(F.m2), D1.h

[0145] FROM F, D7, D3

[0146] WHERE F.f1 = D7.p and F.f3 = D3.p and F.x = 11 and D3.w > 11 GROUP BY F.y, D7.h;

[0147] [{F, D7, D3}, {Q10}]

[0148] [{F, D7}, {Q9, Q10}]

[0149] CREATE MATERIALIZED VIEW MV3 AS

[0150] SELECT F.n, F.y, D7.y, D7.h, D7.c, F.x, F.f2, F.f3, COUNT(F.m2), SUM(F.m1)

[0151] FROM F, D7

[0152] WHERE F.f1 = D7.p and F.x IN(4,6,11)

[0153] GROUP BY F.n, F.y, F.x, D7.y, D7.h, D7.c, F.f2, F.f3;

[0154] In the above example, the join set {F, D7, D2} is associated with the query block set {Q9} and has a join graph involving tables F, D7, and D2 and their join edges. Similarly, the join set {F, D7, D3} is associated with the query block set {Q10} and has a join graph involving tables F, D7, and D3 and their join edges. Both join sets {F, D7, D2} and {F, D7, D3} contain table F and D7 and the exact same join predicate joining F and D7 (i.e., F.fl = D7.p). Therefore, the intersection join set operation can be applied to form a result join set representing MV3, which can be used to rewrite both query blocks Q9 and Q10.

[0155] MV3 is formed from the unification of the filter predicates and the merging of the projected columns and the grouping lists in the query blocks, allowing MV3 to be used to rewrite both query blocks Q9 and Q10. In the above example, the predicate F.x IN(4, 6, 11) in MV3 unifies the predicate F.x IN(4, 6) in query block Q6 and the predicate F.x = 11 in query block Q9. The above join set operation also merges the projected columns in query blocks Q9 and Q10 using the statement SELECT F.n, F.y, D7.y, D7.h, D7.c, F.x, F.f2, F.f3, COUNT(F.m2), SUM(F.ml).

[0156] Subset

[0157] A subset of a join set A is another set containing only join edges from join set A, but possibly not all join edges of A. When join set B is a subset of join set A, the relationship between join set B and A can be represented as This means that join set B contains only some of the join edges of join set A.

[0158] In one embodiment, a subset join set operation can be applied when one join set is a subset of another join set. The join set that is a subset of another join set is established as the result join set and its associated query block set is the union of the query block sets of the constituent join sets.

[0159] More specifically, a subset join set operation can be applied to rewrite the query block set associated with a first constituent join set when the first constituent join set includes all tables involved in a second constituent join set; the second constituent join set includes at least a join edge not involved in the first constituent join set.

[0160] Provided below is an example of a subset join set operation that generates a result join set representing MV.

[0161] Q11:

[0162] SELECT F.x, D1.y, D2.z, SUM(F.m1)

[0163] FROM F, D1, D2

[0164] WHERE F.f1 = D1.k and F.f2 = D2.k and F.y = 5 and D1.c = 9 and D2.s < 25 GROUP BY F.x, D1.y, D2.z;

[0165] [{F, D1, D2}, {Q11}]

[0166] Q12:

[0167] SELECT F.x, D1.h, COUNT(F.m2)

[0168] FROM F, D1

[0169] WHERE F.f1 = D1.k and F.y = 7 and D1.g = 7 and D1.c = 33

[0170] GROUP BY F.x, D1.h;

[0171] [{F, D1}, {Q12}]

[0172] [{F, D1}, {Q11, Q12}]

[0173] CREATE MATERIALIZED VIEW MV4 AS

[0174] SELECT F.x, D1.y, D1.h, D1.c, D1.g, F.y, F.f2, COUNT(F.m2), SUM(F.m1) FROM F, D1

[0175] WHERE F.f1 = D1.k and D1.c IN (9, 33) and F.y IN (5, 7)

[0176] GROUP BY F.x, F.y, D1.y, D1.h, D1.c, D1.g, F.f2;

[0177] In the above example, the join set {F, Dl, D2} is associated with the query block set {Ql l} and has a join graph involving tables F, Dl, and D2 and their join edges. Similarly, the join set {F, Dl} is associated with the query block set {Q12} and has a join graph involving tables F and Dl and their join edges. The join set {F, Dl} contains only tables from the join set {F, Dl, D2} but not all tables. Thus, the join set {F, Dl} is a subset of the join set {F, Dl, D2} and the join predicates for the joins F and Dl are identical in both join sets. Thus, a subset join set operation can be applied to generate a result join set representing MV4, which can be used to rewrite both query blocks Ql l and Q12.

[0178] MV4 is formed from the unification of the filter predicates and the merging of the select and group lists, allowing MV4 to be used to rewrite both query blocks Ql l and Q12. In the above example, the predicate Dl.c IN (9, 33) in MV4 unifies the predicate Dl.c = 9 in query block Ql l and the predicate Dl.c = 33 in query block Q12. Similarly, the predicate F.y IN (5, 7) in MV4 unifies the predicate F.y = 5 in query block Ql l and the predicate F.y = 7 in query block Q12. The above join set operation also uses the statement SELECT F.x, Dl.y, Dl.h, Dl.c, Dl.g, F.y, F.f2, COUNT(F.m2), SUM(F.ml) to merge the projected columns in query blocks Ql l and Q12.

[0179] Superset

[0180] A superset of a join set A is another join set that contains all the join edges from join set A and also contains other join edges. When join set B is a superset of join set A, the relationship between join sets B and A can be represented as This means that join set B contains all the join edges from join set A but can also contain other join edges.

[0181] In one embodiment, a superset join set operation can be applied when a first join set is a superset of a second join set and is invariant with respect to the second join set. The superset join set is identified as the result join set. The result join set is not equivalent to one of the two constituent join sets.

[0182] More specifically, a superset join set operation can be applied to rewrite a query block set associated with a first constituent join set when the first join set includes all the join edges involved in the second join set and the first join set includes at least one join edge not involved in the second join set. The join set that is a superset of the other join set is established as the result join set and its associated query block set is the union of the query block sets of the constituent join sets.

[0183] Provided below is an example of a superset join operation that generates a superset join set of results representing MVs.

[0184] Q13:

[0185] SELECT F.n, SUM(F.m1), D1.m

[0186] FROM F, D1

[0187] WHERE F.f1 = D1.k and F.x = 6 and D1.y = 25

[0188] GROUP BY F.n, D1.m;

[0189] [{F, D1}, {Q13}]

[0190] Q14:

[0191] SELECT F.y, MIN(F.m2), D1.h, D5.z

[0192] FROM F, D1, D5

[0193] WHERE F.f1 = D1.k and F.fk5 = D5.pk and F.x = 11 and D1.y = 33 and D5.g > 6

[0194] GROUP BY F.y, D1.h, D5.z;

[0195] [{F, D1, D5}, {Q14}]

[0196] [{F, D1, D5}, {Q13, Q14}]

[0197] Create materialized view MV5 as

[0198] SELECT F.n, F.y, D1.m, D1.h, D5.z, D1.y, D5.g, F.x, MIN(F.m2),

[0199] SUM(F.m1)

[0200] FROM F, D1, D5

[0201] WHERE F.f1 = D1.k and F.fk5 = D5.pk and F.x IN (6, 11) and D1.y IN (25, 33)

[0202] GROUP BY F.n, F.y, F.x, D1.y, D1.m, D1.h, D5.z, D5.g;

[0203] In the above example, the join set {F, D1} is associated with the query block set {Q13} and has a join graph involving tables F and D1 and their join edges. Similarly, the join set {F, D1, D5} is associated with the query block set {Q14} and has a join graph involving tables F, D1, and D5 and their join edges. In query block Q11, tables F and D5 are joined based on the join condition F.fk5 = D5.pk. In query blocks Q13 and Q14, tables F and D1 are joined based on the join condition F.f1 = D1.pk. Here, the join between the join set {F, D1} and D5 is an invariant join, which can be denoted as Invariant({F, D1}, D5).

[0204] The join set {F, D1} contains only the tables from the join set {F, D1, D5} but not all the tables. Thus, the join set {F, D1} is a subset of the join set {F, D1, D5}. Because the join set {F, D1} is invariant with respect to its join with table D5, the superset join set operation can be used to form a result join set denoted as MV5, which can be used to rewrite both query blocks Q13 and Q14.

[0205] MV5 is formed from the unification of the filter predicates and the merging of the select and group list, which enables MV5 to be used to rewrite both query blocks Q13 and Q14. In the above example, the predicate F.x IN(6, 11) in MV5 unifies the predicate F.x = 6 in query block Q13 and the predicate F.x = 11 in query block Q14. Similarly, the predicate D1.y IN(25, 33) in MV5 unifies the predicate D1.y = 25 in query block Q13 and the predicate D1.y = 33 in query block Q14. To satisfy the 3rd invariance condition, the filter predicate on D5 is not included in the definition of MV5. The above join set operation also merges the projected columns in query blocks Q13 and Q14 using the SQL statement SELECT F.n, F.y, D1.m, D1.h, D5.z, D1.y, D5.g, F.x, MIN(F.m2), SUM(F.m1).

[0206] Union

[0207] The union of two join sets A and B is another join set that contains all the join predicates that exist in join sets A and B. The symbol ∪ is used for union. A ∪ B denotes the union set that contains all the elements of sets A and B.

[0208] In one embodiment, when a first join set intersects with a second join set, a union join set operation can be applied, their intersection is non-empty, and the tables in the intersection are invariantly joined with the other tables of the two constituent join sets. (The other tables belong to either of the two join sets but are not in the intersection.) The resulting join set is not equivalent to either of the constituent join sets.

[0209] More specifically, when a first constituent join set includes at least one table not involved in a second constituent join, a union join set operation can be applied to generate a resulting join set sent and a resulting set of query blocks associated with the resulting join set; the second constituent join set includes at least one table not involved in the first constituent join; and the first constituent join set and the second constituent join set contain at least one table in common and the tables in the intersection have an invariant join with the tables not in the intersection. The resulting join set includes all join edges involved in the first and second constituent joins; and the resulting set of query blocks is the union of the sets of query blocks associated with the first and second constituent join sets.

[0210] Provided below is an example of a union join set operation that generates a resulting join set representing an MV.

[0211] Q15:

[0212] SELECT F.n, D1.m, SUM(F.m1)

[0213] FROM F, D1

[0214] WHERE F.fk1 = D1.pk and F.x = 6 and D1.z = 25 GROUP BY F.n, D1.m;

[0215] [{F, D1}, {Q15}]

[0216] Q16:

[0217] SELECT F.y, D2.w, MAX(F.m2)

[0218] FROM F, D2

[0219] WHERE F.fk2 = D2.pk and F.x = 12 and D2.g > 7 GROUP BY F.y, D2.w;

[0220] [{F, D2}, {Q16}]

[0221] [{F, D1, D2}, {Q15, Q16}]

[0222] create materialized view MV6 as

[0223] SELECT F.n, F.y, D1.m, D2.w, D1.z, D2.g, F.x, MAX(F.m2), SUM(F.m1) FROM F, D1, D2

[0224] WHERE F.fk1 = D1.pk and F.fk2 = D2.pk and F.x IN(6, 12) GROUP BY F.n, F.y, F.x, D1.m, D2.w, D1.z, D2.g;

[0225] In the above example, the join set {F, D1} is associated with the query block set {Q15} and has a join graph involving tables F and D1 and their join edges. Similarly, the join set {F, D2} is associated with the query block set {Q16} and has a join graph involving tables F and D2 and their join edges. In query block Q15, F.fk1 is a non-null foreign key referencing the primary key D1.pk. In query block Q16, F.fk2 is a non-null foreign key referencing the primary key D2.pk. F is invariant with respect to its joins with D1 and D2. This can be expressed as Invariant({F, D1}, D2} and Invariant({F, D2}, D1).

[0226] MV6 is formed by unifying the filter predicates and the merging of the select and group by lists, which enables MV6 to be used to rewrite both query blocks Q15 and Q16. In the above example, the predicate F.x IN(6, 12) in MV6 unifies the predicate F.x = 6 in query block Q15 and the predicate F.x = 12 in query block Q16. To satisfy the 3rd invariance condition, the filter predicates with respect to D1 and D2 are not included in the definition of MV6. The above join set operation also merges the projected columns in query blocks Q15 and Q16 using the statement SELECT F.n, F.y, D1.m, D2.w, D1.z, D2.g, F.x, MAX(F.m2), SUM(F.m1) FROM F, D1, D2.

[0227] Automatic generation of materialized views

[0228] Figure 4 is a flowchart depicting a process for automatically generating materialized views from a list of query blocks using ECSE. Figure 4C and Figure 4D together provide an example of the ECSE algorithm.

[0229] Referring to Figure 4 , at operation 400, if a query block contains fewer than two tables, then the query block is pruned from the list of query blocks. A join graph is generated for each query block in the list of query blocks. The fact, dimension, and branch tables in each join graph are identified, and invariant joins are detected.

[0230] In one embodiment, if the join graph has many-to-many join arcs, then the join graph partitioning and reduction at operation 400 is performed, referred to herein as Heuristic A. Figure 5A An example flowchart for performing Heuristic A 510 is illustrated. Referring to Figure 5A , the system receives a join graph from a list of join graphs at operation 511. The system determines whether the join graph contains many-to-many join arcs at operation 512. A join graph contains many-to-many join arcs when multiple records in a fact table are associated with multiple records in another fact table. If the join graph contains many-to-many join arcs, then the system can remove the dimension table nodes involved in the many-to-many join arcs from the join graph. If the join graph does not contain many-to-many join arcs, then the join graph is not removed at operation 514.

[0231] Referring to Figure 4 , a given list of query blocks is partitioned into sublists such that each sublist contains join graphs with a common fact table at operation 410.

[0232] Referring to Figure 4 , lists ALST and NLST are created at operation 420. ALST stores join sets and associated query block sets derived from join graphs generated at operation 400. NLST stores result join sets and associated query block sets resulting from join set operations.

[0233] Referring to Figure 4 , join sets and query block sets are derived from join graphs and inserted into ALST at operation 430.

[0234] Operations 440-470 to identify materialized views from the list of query blocks include join set operations applied to constituent join sets to form result join sets or update at least one constituent join set.

[0235] Join set operations are described in pseudo code. In the pseudo code, X refers to a join set in a sublist and its associated query block set, and Y refers to another join set in the same sublist and its associated query block set. Here, pairwise comparisons are made between each item in a sublist and every other item in the same sublist. Self-comparisons are avoided by ensuring that X is not equal to (i.e., <>) Y. Each join set operation compares one join set in a sublist and its associated query block set to another join set and corresponding associated query block set. The join set operation updates the join sets and their associated query block sets or generates a new item Z that includes a result join set and corresponding associated query block set, where the result join set can be used to rewrite each query block in its query block set.

[0236] Operation 440 generates new result join sets using extended cover subexpression (ECSE) techniques. For example, multiple different queries in the working set can have the same join graph, and thus be "covered" by the same join subexpression. The result join sets are then used to form candidate MV sets.

[0237] Figure 4A is a flowchart depicting a process for automatically generating materialized views from a list of query blocks using the ECSE algorithm. Referring to Figure 4A , operations 441-442 are iteratively performed as a loop to extract CSEs. Each iteration of the loop can result in a new join set, a change in the state of a join set, or a removal of a join set. The updated join sets and associated query block sets resulting from the join set operations are stored in the ALST. The new join sets and associated query block sets resulting from the join set operations are stored in the NLST. After performing operations 441-442, the NLST is appended to the ALST.

[0238] Referring to Figure 4A , at operation 441, an equivalent join set operation is performed to prune join sets and query block sets. For two different items X and Y stored in the ALST, if the join sets of items X and Y are the same, then an equivalent set operation is performed to remove item Y from the ALST. The remaining join set X.jnset can be used to rewrite both query blocks X.qbset and Y.qbset. Provided below is a pseudo-code description of operation 441. Here, "<>" denotes a "not equal to" comparison operator.

[0239] If (X <> Y A X.jnset = Y.jnset)

[0240] {

[0241] X.qbset = X.qbset U Y.qbset;

[0242] Remove Y from ALST;

[0243] }

[0244] In the pseudo-code description above, X.qbset is the associated query block set for join set X.jnset. Y.qbset is the associated query block set for join set Y.jnset. Symbolically, the relationship between join set X.jnset and its associated query block set X.qbset can be written as [{X.jnset}, {X.qbset}]. Similarly, the relationship between join set Y.jnset and its associated query block set Y.qbset can be written as [{Y.jnset}, {Y.qbset}].

[0245] When the join sets derived from the join graph of two query blocks are the same, an equivalent join set operation is performed to prune the join set derived from one of the two query blocks and the set of query blocks. The new relationship between the remaining join set X.jnset and its associated set of query blocks can be written as [{X.jnset}, {X.qbset U Y.qbset}], which means that the two query block sets X.qbset and Y.qbset derived from the two query blocks can now be rewritten using the join set X.jnset.

[0246] Referring to Figure 4A At operation 442, an intersection operation is performed to identify intersection join sets. For two different items X and Y stored in the ALST, if the join set of item X is not a subset of the join set of item Y, the join set of item Y is not a subset of the join set of item X, and there is an overlap between the join set of item X and the join set of item Y, a new join set is generated that represents the overlapping portion of the two join sets. The new join set is added to the NLST. Provided below is a pseudo code description of operation 442.

[0247]

[0248] In the pseudo code description above, an intersection join set operation is performed to generate a new join set Z.jnset. For two different items X and Y stored in the ALST, if the join set of item X (X.jnset) is not a subset of the join set of item Y (Y.jnset), the join set of item Y (Y.jnset) is not a subset of the join set of item X (X.jnset), and there is an overlap between Y.jnset and X.jnset (e.g., containing at least one table), the intersection join set operation is performed to generate a new join set Z.jnset. Z.jnset contains the common portion of the join sets Y.jnset and X.jnset, and can be used to rewrite both query block sets X.qbset and Y.quset. The relationship between the new join set Z.jnset and its associated set of query blocks can be written as [{Z.jnest}, {X.qbset U Y.qbset}].

[0249] Referring to Figure 4A After performing operations 441-442, the NLST is appended to the ALST at operation 443.

[0250] Referring to Figure 4At operation 450, a union joinset is identified based on invariance. For two different items X and Y stored in the ALST, if the joinset of item Y is not a subset of the joinset of item X, the joinset of item X is not a subset of the joinset of item Y, and there is an overlap between the joinset of item X and the joinset of item Y, and there is an invariant join, then a union joinset operation is performed to generate a new joinset representing the union of the joinset of item X and the joinset of item Y, and the new joinset can be used to rewrite both the query block set of item X and Y. The new joinset is added to the ALST. Provided below is a pseudo code description of operation 450.

[0251]

[0252] In the pseudo code description above, a union joinset operation is performed to generate a new joinset Z.jnset. For two different items X and Y stored in the ALST, if the joinset of item Y (Y.jnset) is not a subset of the joinset of item X (X.jnset), the joinset of item X (X.jnset) is not a subset of the joinset of item Y (Y.jnset), there is an overlap between Y.jnset and X.jnset, and there is an invariant join, then a union joinset operation is performed to generate a new joinset Z.jnset. Z.jnset contains the union joinset of Y.jnset and X.jnset, and can be used to rewrite both the query block set X.qbset and Y.qbset. The relationship between the new joinset Z.jnset and its associated query block set can be written as [{Z.jnset}, {X.qbset U Y.qbset}]. The new joinset Z.jnset is added to the ALST.

[0253] Referring to Figure 4 At operation 460, an equivalent joinset operation is performed to prune joinsets and query block sets.

[0254] Figure 4B is a flowchart depicting the process of operation 470. Referring to Figure 4B Operations 471-472 are iteratively performed as a loop to identify a subset or superset of a joinset. Each iteration of the loop can change the state of the joinset. The updated joinset and associated query block set resulting from the joinset operation are stored in the ALST.

[0255] Referring to Figure 4B At operation 471, a subset joinset operation is performed to identify a subset joinset. For two different items X and Y stored in the ALST, if the joinset of item X is a subset of the joinset of item Y, then the query block set of item X is updated to contain the query block set of item X and the query block set of item Y. Provided below is a pseudo code description of operation 471.

[0256]

[0257] X.qbset = X.qbset U Y.qbset;

[0258] In the above pseudo-code description, when the join set of item X (X.jnset) is a subset of the join set of item Y (Y.jnset) and there is an invariant join, the query block set derived from the join graph of query block X (X.qbset) is updated to contain both the query block set of item X and the query block set of item Y. The new relationship between the join set X.jnset and its associated query block set can be written as [{X.jnest}, {X.qbset U Y.qbset}], which means that the join set X.jnset can now be rewritten with two query block sets X.qbset and Y.qbset.

[0259] Referring to Figure 4B At operation 472, a superset join set operation is performed to identify superset join sets, and if the join set of item Y in the ALST is a subset of the join set of item X in the same list and there is an invariant join, then the query block set of item X is updated to contain both the query block set of item X and the query block set of item Y. Provided below is a pseudo-code description of operation 472.

[0260] X.qbset = X.qbset U Y.qbset;

[0261] In the above pseudo-code description, when the join set of item Y (Y.jnset) is a subset of the join set of item X (X.jnset) and there is an invariant join, the query block set of item X (X.qbset) is updated to contain both the query block set of item X (X.qbset) and the query block set of item Y (Y.qbset). The new relationship between the join set X.jnset and its associated query block set can be written as [{X.jnest}, {X.qbset U Y.qbset}], which means that the join set X.jnset can now be rewritten with two query block sets X.qbset and Y.qbset.

[0262] Referring to Figure 4 At operation 480, join set and associated query block set reduction, referred to herein as heuristics B, C, and D, are performed.

[0263] In one embodiment, if the number of joins contained by a join set is below a given threshold, then join set partitioning and reduction, referred to herein as heuristic B, is performed. Figure 5B An example flowchart for performing heuristic B 520 is illustrated. Referring to Figure 5BAt operation 521, the system receives a join set and its associated query block set from the list of join sets and associated query block sets. At operation 522, the system determines whether the number of joins contained by the join set is below a given threshold. For example, the given threshold Ψ can be computed as half of the average number of joins in all query blocks in all queries of a given workload. If the number of joins contained by the join set is below the given threshold, then at operation 523, the system removes the join set and its associated query block set from the list of join sets and associated query block sets. If the number of joins contained by the join set is not below the given threshold, then at operation 524, the join set and its associated query block set are not removed.

[0264] Provided below is a pseudo-code description of heuristic B.

[0265]

[0266] Remove X from ALST;

[0267] Continue;

[0268] }

[0269] In the pseudo-code description above, if the number of joins contained by X.jnest is below a given threshold Ψ, then the join set X.jnest and its associated query block set X.qbset are pruned from the ALST.

[0270] In one embodiment, if the number of elements of the associated query block set of a join set is below a given threshold, then join set partitioning and reduction is performed, referred to herein as heuristic C. Figure 5C An example flowchart for performing heuristic C 530 is illustrated. Reference is made to Figure 5C At operation 531, the system receives a join set and its associated query block set from the list of join sets and associated query block sets. At operation 532, the system determines whether the number of elements contained by the associated query block is below a given threshold. For example, the given threshold β can be set to 2, which ensures that only those MVs that can rewrite at least 2 query blocks are recommended. If the number of elements contained by the associated query block is below the given threshold, then at operation 533, the join set and its associated query block set are removed from the list of join sets and associated query block sets. If the number of elements contained by the associated query block is not below the given threshold, then at operation 534, the join set and its associated query block set are not removed.

[0271] Provided below is a pseudo-code description of heuristic C.

[0272] If (|X.qbset| < β)

[0273] {

[0274] Remove X from ALST;

[0275] Continue;

[0276] }

[0277] In the above pseudo-code description, if the number of elements of X.qbset is below a given threshold β (e.g., 2), then prune the join set X.jnest and its associated query block set X.qbset from the ALST.

[0278] In one embodiment, if there is a maximal join set, then perform join set partitioning and reduction, referred to herein as heuristic D. Figure 5D An example flowchart for performing heuristic D 540 is illustrated. Referring to Figure 5D , the system receives a join set and its associated query block set from a list of join sets and associated query block sets at operation 541. The system determines whether there is a maximal join set in the list of join sets at operation 542. For example, if join set J k is a subset of join set J i and the associated query block set of J k is a subset of the associated query block set of J i , then join set J i is considered maximal with respect to join set J k . If there is a maximal join set in the list of join sets, then the system removes the join set and its associated query block set from the list of join sets and associated query block sets at operation 543. If there is no maximal join set in the list of join sets, then the join set and its associated query block set are not removed at operation 544.

[0279] Provided below is a pseudo-code description of heuristic D.

[0280]

[0281] Remove X from ALST

[0282] Referring to Figure 4 , at operation 490, perform uniformation of filter predicates and merge of select or group lists for each item in the ALST based on its join set and query block set, and form candidate materialized views.

[0283] Referring to Figure 4 , perform join set and associated query block set reduction at operation 480, referred to herein as heuristics E and F.

[0284] In one embodiment, if the ratio of the number of rows of the join set to the number of rows of the fact table is greater than a given threshold, then perform join set partitioning and reduction, referred to herein as heuristic E.Figure 5E An example flowchart for performing heuristic E 550 is illustrated. Referring to Figure 5E , the system receives a join set and its associated query block set from a list of join sets and associated query block sets at operation 551. The system determines whether the ratio of the number of rows of the fact table of the join set to the number of rows is greater than a given threshold at operation 552. In one example, the estimate of the number of rows of the join set takes into account the join predicates, the unioned filter predicates, and the grouping columns. If the ratio of the number of rows of the fact table of the join set to the number of rows is greater than the given threshold, then the system removes the join set and its associated query block set from the list of join sets and associated query block sets at operation 553. If the ratio of the number of rows of the fact table of the join set to the number of rows is not greater than the given threshold, then the join set and its associated query block set are not removed at operation 554.

[0285] In another embodiment, if the estimated cost reduction of all the rewritten query blocks in its associated query block set is higher than a given threshold, then the join set partitioning and reduction is performed, referred to herein as heuristic F. Figure 5F An example flowchart for performing heuristic F 560 is illustrated. Referring to Figure 5F , the system receives a join set and its associated query block set from a list of join sets and associated query block sets at operation 561. The system determines whether the estimated cost reduction of all the rewritten query blocks in its associated query block set is higher than a given threshold at operation 562. For example, if [Cost(RW(Q1)) + Cost(RW(Q2)) + Cost(RW(Q3))] / [Cost(Q1) + (Cost(Q2) + Cost(Q3))] > Ω, then the associated query block set {Q1, Q2, Q3} of the join set will be pruned. If the estimated cost reduction of all the rewritten query blocks in its associated query block set is higher than a given threshold, then the system removes the join set and its associated query block set from the list of join sets and associated query block sets at operation 563. If the estimated cost reduction of all the rewritten query blocks in its associated query block set is not higher than a given threshold, then the join set and its associated query block set are not removed at operation 564.

[0286] Each resulting join set and associated query block set can be used to generate a candidate MV. Referring to Figure 4 An optimal global greedy (OPTGG) algorithm can decide whether to recommend a candidate MV based on the net benefit to workload performance at operation 492. The OPTGG takes into account constraints such as storage space, MV maintenance cost, etc.

[0287] Figure 6 is a flowchart depicting the process for the OPTGG algorithm. Referring to Figure 6The OPTGG algorithm first orders the candidate MVs generated from the resulting join set at step 610 according to their estimated creation cost and a reduction factor. The OPTGG algorithm then invokes the MV rewrite module for each candidate MV at step 620. The OPPTGG algorithm executes the workload query with MV rewrite if eligible at step 630. The OPPTGG algorithm then compares the estimated cost of the workload query with and without MV rewrite at step 640. The MV is recommended if it provides a net benefit to the workload performance at step 650.

[0288] Hardware Overview

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

[0290] For example, Figure 7 is a block diagram that illustrates a computer system 700 upon which an embodiment of the application can be implemented. Computer system 700 includes a bus 702 or other communication mechanism for communicating information, and a hardware processor 704 coupled with bus 702 for processing information. Hardware processor 704 can be, for example, a general purpose microprocessor.

[0291] In some embodiments, a coprocessor can reside on the same chip as the hardware processor or processor core. Examples of such coprocessors include a data analytics accelerator (DAX) coprocessor and a single instruction multiple data (SIMD) processor.

[0292] A DAX coprocessor enables database operations to run directly in the coprocessor while the hardware processor core executes other instructions. Such operations include (1) scanning an array to find elements that match (or are greater than or less than) an input value and returning a bit vector with bits set for matches; (2) selecting elements from an array based on a bit vector; and (3) in a set operation, given an input set of integers, determining how many of them also exist in another set.

[0293] SIMD processors perform the same operation on multiple data items simultaneously. SIMD processors leverage data-level parallelism by executing a single instruction on data in multiple registers or sub-registers. Therefore, the throughput per instruction can be increased accordingly.

[0294] Computer system 700 also includes main memory 706, such as random access memory (RAM) or other dynamic storage device, coupled to bus 702, for storing instructions and information to be executed by processor 704. Main memory 706 can also be used to store temporary variables or other intermediate information during the execution of instructions by processor 704. When stored in non-transient storage media accessible to processor 704, these instructions make computer system 700 a dedicated machine customized to perform the operations specified in the instructions.

[0295] The computer system 700 also includes a read-only memory (ROM) 708 or other static storage device coupled to the bus 702 for storing static information and instructions for the processor 704. Storage devices 710 (such as disks, optical discs, or solid-state drives) are provided and coupled to the bus 702 for storing information and instructions.

[0296] Computer system 700 can be coupled to display 712 (such as a cathode ray tube (CRT)) via bus 702 for displaying information to the computer user. Input device 714, including alphanumeric keys and other keys, is coupled to bus 702 for transmitting information and command selections to processor 704. Another type of user input device is cursor control 716 (such as a mouse, trackball, or arrow keys) for transmitting directional information and command selections to processor 704 and for controlling cursor movement on display 712. Such input devices typically have two degrees of freedom on two axes (a first axis (e.g., x) and a second axis (e.g., y)), allowing the device to specify a position in a plane.

[0297] Computer system 700 may implement the techniques described herein using custom hardwired logic, one or more ASICs or FPGAs, firmware and / or program logic (which, in conjunction with the computer system, make computer system 700 a special-purpose machine or program computer system 700 as a special-purpose machine). According to an embodiment, computer system 700 performs the techniques described herein in response to processor 704 executing one or more sequences of one or more instructions contained in main memory 706. These instructions may be read into main memory 706 from another storage medium (such as storage device 710). Execution of the sequence of instructions contained in main memory 706 causes processor 704 to perform the processing steps described herein. In alternative embodiments, hardwired circuitry may be used instead of software instructions or in combination with software instructions.

[0298] The term "storage media" as used herein refers to any non-transitory media that store data and / or instructions that cause a machine to operate in a specific fashion. Such storage media can comprise non-volatile media and / or volatile media. Non-volatile media includes, for example, optical disks, magnetic disks, or solid-state drives, such as storage device 710. Volatile media includes dynamic memory, such as main memory 706. Common forms of storage media include, for example, a floppy disk, a flexible disk, hard disk, solid- state drive, magnetic tape, or any other magnetic data storage medium, a CD-ROM, any other optical data storage medium, any physical medium with patterns of holes, a RAM, a PROM, and EPROM, a FLASH- EPROM, NVRAM, any other memory chip or cartridge.

[0299] Storage media are distinct from, but can be used in combination with, transmission media. Transmission media participate in transferring information between storage media. For example, transmission media includes coaxial cables, copper wire, and optical fibers, including the wires that comprise bus 702. Transmission media can also take the form of acoustic or light waves, such as those generated during radio frequency and infrared data communications.

[0300] Carrying one or more sequences of one or more instructions to the processor 704 for execution can involve various forms of media. 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 computer system 700 can receive the data on the telephone line and use an infra-red transmitter to convert the data to an infra-red signal. An infra-red detector can receive the data carried in the infra-red signal and appropriate circuitry can place the data on bus 702. Bus 702 carries the data to main memory 706, from which processor 704 retrieves and executes the instructions. The instructions received by main memory 706 can optionally be stored on storage device 710 either before or after execution by processor 704.

[0301] Computer system 700 also includes a communication interface 718 coupled to bus 702. Communication interface 718 provides a two-way data communication coupling to a network link 720 that is connected to a local network 722. For example, communication interface 718 can be an integrated services digital network (ISDN) card, cable modem, satellite modem, or a modem to provide a data communication connection to a corresponding type of telephone line. As another example, communication interface 718 can be a local area network (LAN) card to provide a data communication connection to a compatible LAN. Wireless links can also be implemented. In any such implementation, communication interface 718 sends and receives electrical, electromagnetic or optical signals that carry digital data streams representing various types of information.

[0302] Network link 720 typically provides data communication through one or more networks to other data devices. For example, network link 720 can provide a connection through local network 722 to a host computer 724 or to data equipment operated by an Internet Service Provider (ISP) 726. ISP 726 in turn provides data communication services through the worldwide packet data communication network now commonly referred to as the "Internet" 728. Local network 722 and Internet 728 both use electrical, electromagnetic or optical signals that carry digital data streams. The signals through the various networks and the signals on network link 720 and through communication interface 718, which carry the digital data to and from computer system 700, are example forms of transmission media.

[0303] Computer system 700 can send messages and receive data, including program code, through the network(s), network link 720 and communication interface 718. In the Internet example, server 730 might transmit a requested code for an application program through Internet 728, ISP 726, local network 722 and communication interface 718.

[0304] The received code can be executed by processor 704 as it is received, and / or stored in storage device 710, or other non-volatile storage for later execution.

[0305] Software Overview

[0306] Figure 8 is a block diagram of a software system 800 that can be employed for controlling the operation of the computer system 700. The software system 800 and its components, including their connections, relationships and functions, is meant to be exemplary only and not meant to limit implementations of the example embodiment(s). Other software systems suitable for implementing the example embodiment(s) can have different components, including components with different connections, relationships and functions.

[0307] The software system 800 is provided for directing the operation of computer system 700. The software system 800, which can be stored in system memory (RAM) 706 and on fixed storage (e.g., hard disk or flash memory) 710, includes a kernel or operating system (OS) 810.

[0308] OS 810 manages low-level aspects of computer operation, including managing execution of processes, memory allocation, file input and output (I / O), and device I / O. One or more applications, represented as 802A, 802B, 802C... 802N, can be "loaded" (e.g., transferred from fixed storage 710 into memory 706) for execution by system 700. The applications or other software intended for use on system 700 can also be stored as a downloadable computer

[0309] Software system 800 includes graphical user interface (GUI) 815, for receiving user commands and data in a graphical (e.g., "point and click" or "touch gesture") fashion. These inputs, in turn, can be acted upon by system 800 in conjunction with results of receiving certain data that is, otherwise, sensed or captured, in conjunction with operating system 810 and / or application program(s) 802. GUI 815 also serves to display results of operation of system 800, with user inputs being received, processed and generated responses returned as outputs by system 800.

[0310] OS 910 can execute directly on bare hardware 820 of system 800 (e.g., processor(s) 704). Alternatively, a hypervisor or virtual machine monitor (VMM) 830 can be interposed between bare hardware 820 and OS 810. In this configuration, VMM 830 acts as a software "cushion" or virtualization layer between OS 810 and bare hardware 820 of system 700.

[0311] VMM 830 instantiates and runs one or more virtual machine instances ("guest machines"). Each guest machine includes a "guest" operating system (such as OS 810), and one or more applications designed to execute on the guest operating system (such as application(s) 802). VMM 830 presents the guest operating systems with a virtual operating platform and manages execution of the guest operating systems.

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

[0313] In other cases, guest operating systems can be specifically designed or configured to run on VMM 830 for improved efficiency. In these instances, the guest operating system is "aware" that it is running on a virtual machine monitor. In other words, VMM 830 can provide paravirtualization to guest operating systems in some situations.

[0314] The purpose of presenting the basic computer hardware and software described above is to illustrate the basic underlying computer components that can be used to implement one or more example embodiments. However, the one or more example embodiments are not necessarily limited to any particular computing environment or computing device configuration. Instead, according to this disclosure, the one or more example embodiments can be implemented in any type of system architecture or processing environment that will be understood by those skilled in the art to be capable of supporting the features and functionality of the one or more example embodiments presented herein.

[0315] cloud computing

[0316] This article generally uses the term "cloud computing" 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 allows for the rapid provisioning and release of resources with minimal management effort or service provider interaction.

[0317] Cloud computing environments (sometimes called cloud environments or the cloud itself) can be implemented in various 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, private cloud environments are generally used only by a single organization or within a single organization. Community clouds are designed to be shared by several organizations within a community; while hybrid clouds include two or more types of clouds (e.g., private, community, or public) bound together by data and application portability.

[0318] In general, cloud computing models enable some of those responsibilities previously provided by an organization's own information technology department to instead be delivered as a service layer within a cloud environment, for consumption by customers (either within or outside of 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 customers use software applications running on cloud infrastructure, while the SaaS provider manages or controls the underlying cloud infrastructure and applications. Platform as a Service (PaaS), in which customers can use software programming languages and development tools supported by the PaaS provider to develop, deploy, and otherwise control their own applications, while the PaaS provider manages or controls other aspects of the cloud environment (i.e., everything below the run-time execution environment). Infrastructure as a Service (IaaS), in which customers can deploy and operate arbitrary software applications, and / or provision processing, storage, networks, and other fundamental computing resources, while 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 customers use a database server or database management system running on cloud infrastructure, while the DbaaS provider manages or controls the underlying cloud infrastructure, applications, and servers, including one or more database servers.

[0319] In the foregoing specification, embodiments of the application have been described with reference to specific embodiments thereof. It will, however, be evident that various modifications and changes can be made thereto without departing from the broader spirit and scope of the application. The specification and drawings are, accordingly, to be regarded in an illustrative rather than a restrictive sense. The sole and exclusive indicator of the scope of the application, and what is intended by the applicants to be the scope of the application, is the literal and equivalent scope of the claims issued after publication of this application and any subsequent correction.

Claims

1. A method for automatically generating materialized views, comprising: Multiple connection sets are derived from the query workload set, wherein each connection set in the multiple connection sets represents a corresponding connection graph for a corresponding set of query blocks from the query workload set, and the corresponding connection graph includes one or more connection edges, each of the one or more connection edges including one or more connection conditions; The join set and associated query block set include a first join set and a first query block set associated with the first join set, and a second join set and a second query block set associated with the second join set, wherein the first join set involves tables not involved in the second join set or the second join set involves tables not involved in the first join set. Join set operations are applied to the constituent join sets and associated query block sets of the plurality of join sets to generate a result join set associated with a corresponding result query block set, the corresponding result query block set comprising each corresponding query block set of each constituent join set in the plurality of join sets, wherein the join graph of the corresponding result query block set is not equivalent to the join graph of at least one constituent join set in the constituent join sets. Based on the result join set associated with the corresponding result query block set, determine the net workload benefit that the materialized view based on the result query block set provides to the set of queries; In response to determining that a materialized view provides net workload benefits to the collection of queries, a materialized view is selected for creation.

2. The method of claim 1, wherein applying the join set operation includes applying the intersection join set operation to generate a result join set and a result query block set associated with the result join set by at least determining that the first join set intersects with the second join set, wherein: The first join set includes at least one first table that is not involved in the second join set; The second join set includes at least a second table that is not involved in the first join set; Both the first join set and the second join set contain multiple tables, excluding the first table and the second table; The resulting join set contains multiple tables common to both the first join set and the second join set, but these multiple tables do not include the first table and the second table. The result query block set includes the first query block set and the second query block set.

3. The method of claim 1, wherein applying a join set operation includes applying a subset join set operation to generate a result join set and a result query block set associated with the result join set by at least determining that the first join set is a subset of the second join set, wherein: The second join set includes all the tables involved in the first join set; The second join set includes at least one table that is not involved in the first join set; The resulting join set includes all the tables involved in the first join set; The result query block set includes the first query block set and the second query block set.

4. The method of claim 1, wherein applying the join set operation includes applying a superset join set operation to generate a result join set and a result query block set associated with the result join set by at least determining that the first join set is a superset of the second join set and remains unchanged with respect to the second join set, wherein: The first join set includes all the tables involved in the second join set; The first join set includes at least one table that is not involved in the second join set; The resulting join set includes all tables involved in the corresponding join graph of the first join set; The result query block set includes the first query block set and the second query block set.

5. The method of claim 1, wherein applying the join set operation includes applying a union join set operation to generate a result join set and a result query block set associated with the result join set by at least determining that the first join set intersects with the second join set and that the intersection is invariant with respect to the first join set and the second join set, wherein: The first join set includes at least one table that is not involved in the second join set; The second join set includes at least one table that is not involved in the first join set; The first join set and the second join set contain at least one common table; The resulting join set includes all tables involved in the first join set; The resulting join set includes all tables involved in the second join set; The result query block set includes the first query block set and the second query block set.

6. The method of claim 1, further comprising: Determining a specific join set from the plurality of join sets involves a many-to-many join between a pair of fact tables; In response to the determination, the application of connection set operations to the specific connection set is abandoned.

7. The method of claim 1, further comprising: The join set operation is applied to a constituent join set that includes the plurality of join sets to form a pool of resulting join sets; Prune one or more resulting join sets from the pool of resulting join sets; After pruning one or more result join sets, for each specific result join set in at least some of the result join sets in the pool of result join sets, a materialized view based on each specific subset of result join sets is determined to provide net workload benefits to the collection of queries.

8. The method of claim 7, wherein pruning one or more resulting join sets comprises: Determining a specific connection graph from a specific set of result connections in the pool involves fewer than a threshold number of connections; as well as In response to making the determination, the specific result join set is pruned from the pool.

9. The method of claim 8, wherein pruning one or more resulting join sets comprises: Determining the corresponding set of query blocks from a specific set of results from the pool involves fewer than a threshold number of query blocks; as well as In response to making the determination, the specific result join set is pruned from the pool.

10. A computing system, comprising: One or more processors; as well as A computer-readable medium having instructions stored thereon, which, when executed by the one or more processors, cause the one or more processors to perform the steps of the method as described in any one of claims 1-9.

11. One or more non-transient computer-readable media storing instructions that, when executed by one or more processors, cause the one or more processors to perform the steps of the method as described in any one of claims 1-9.

12. A computer program product comprising instructions that, when executed by a computer, cause the computer to perform the method as described in any one of claims 1-9.

Images