Automated partitioning of materialized views

Abstract

Techniques are provided for automatically partitioning a materialized view. In one technique, a definition of a materialized view is identified. Based on the definition, a plurality of candidate partitioning schemes are identified. A query indicating one or more of the candidate partition schemes is generated. A query is then executed, where execution of the query produces one or more partition counts, each partition count corresponding to a different one of the one or more candidate partition schemes. A candidate partitioning scheme is selected from among a plurality of candidate partitioning schemes based on the one or more partitioning counts. The materialized view is automatically partitioned based on the candidate partitioning scheme.

Description

Technical Field

[0001] The present disclosure relates to the field of database management, and more particularly to automated partitioning of materialized views (MVs). Background Art

[0002] In a database management system (DBMS), materialized views (MVs) are an important feature of database performance. Optimal mixing of MVs minimizes resource utilization (CPU and IO) and increases application throughput when extracting relatively small amounts of data from very large tables.

[0003] Because of the importance of MVs, identifying the optimal mixture of MVs is an important task. Typically, identifying the optimal mixture of MVs involves identifying a small number of MVs that are of reasonable size, contain a lot of pre-computation for joins and groupings, and can rewrite a large number of workload queries.

[0004] U.S. Patent Application Serial No. 16 / 523,872, which is incorporated herein by reference, describes a scheme for automatically generating MVs. The automated scheme monitors workload query execution, identifies poor query performance, analyzes workload queries, and recommends, implements, verifies, and validates automatically generated MVs to achieve improved query performance.

[0005] Given an automatically generated MV, query processing of such an MV can be improved if the MV is partitioned. In a traditional DBMS, rows are inserted into a table without regard for any type of ordering. Therefore, when a user submits a query that selects data from a table based on a specific value or range of values, the entire table must be scanned to ensure that all rows that meet the criteria are identified. Partitioning is a technique that avoids the need to search an entire table or materialized view (or other database object) in some cases.

[0006] With partitioning, an object such as a database table is split into sub-tables, called "partitions." A common form of partitioning is called range partitioning. With range partitioning, each separate partition corresponds to a specific range of values ​​for one or more columns of a table. For example, a column of a table may store data values ​​that fall within a specific year, and the table may be split into twelve partitions, each corresponding to a month of that year. All rows with a specific month in the date column are then inserted into the partition corresponding to that month. In this example, partitioning the table increases the efficiency of processing queries that select rows based on the month contained in the date column. For example, if a particular query selects all columns where the month is equal to January, then only the partitions associated with the month of January must be scanned.

[0007] However, knowing how to partition MVs (especially complex MVs) efficiently is not simple. There are many factors to consider, and even for the most skilled database administrator, it takes a considerable amount of time to acquire knowledge of the factors.

[0008] The approaches described in this section are possible approaches, but are not necessarily approaches that have been previously conceived or adopted. Therefore, unless otherwise indicated, any approach described in this section should not be considered prior art simply because it is included in this section. BRIEF DESCRIPTION OF THE DRAWINGS

[0009] In the attached picture:

[0010] Figure 1 is a flow chart depicting an example process for automatically partitioning a materialized view in an embodiment;

[0011] Figure 2A-2B is a flowchart depicting an example process for automatically partitioning a materialized view in an embodiment;

[0012] Figure 3 is a block diagram illustrating a computer system upon which embodiments of the present invention may be implemented;

[0013] Figure 4 A block diagram of a basic software system that can be used to control the operation of a computer system. DETAILED DESCRIPTION

[0014] In the following description, for the purpose of explanation, many specific details are set forth in order to provide a thorough understanding of the present invention. However, it is clear that the present invention can be practiced without these specific details. In other examples, well-known structures and devices are shown in the form of block diagrams in order to avoid unnecessary obscurity of the present invention.

[0015] General Overview

[0016] A system and method for automatically partitioning a materialized view (MV) is provided. The MV may have been automatically generated ("Automatic MV"). In one technique, a definition of a materialized view is identified. Based on the definition, multiple candidate partitioning schemes are identified. The partitioning scheme includes a set of one or more columns of a specific type. For each candidate partitioning scheme, a cumulative reference count for the candidate partitioning scheme is determined. The reference count of a column of a specific type is a count of the number of query blocks in which the column appears in a filter predicate. Based on the cumulative reference count of each candidate partitioning scheme, a specific candidate partitioning scheme is selected if the partitioning scheme meets a constraint on a user-specified partition count. A materialized view is automatically partitioned based on the specific candidate partitioning scheme.

[0017] Embodiments improve computer-related techniques for materialized views. Specifically, embodiments relate to efficiently identifying a partitioning scheme for automatically partitioning an MV. Partitioned MVs reduce the time to query the MVs. Likewise, automated MV partitioning reduces user errors and, thus, reduces downtime that may be caused by those errors.

[0018] Basic Concepts

[0019] In order to assist in understanding the example process for automatically partitioning the automatically generated MV (described herein), the following concepts are described: join set, set operation, column type, partition scheme, reference count (RC), cumulative RC and cumulative NDV. These concepts will be mentioned in other subsections of the specific implementation.

[0020] A "join set" is an abstraction of a join graph based on a query block (QB) of a workload query. A join set is a set of join edges and has an associated field (referred to as a QB set) that represents a set of QBs that can be potentially rewritten with respect to a MV based on the join set. For simplicity, a join set can be represented as a simplified set of join edges that does not show columns or relational operators: for example, {F1-D1, D1-B1}, where "F1-D1" represents a join edge between tables F1 and D1, and "D1-B1" represents a join edge between tables D1 and B1.

[0021] "Set operations" are performed on the join sets. Examples of set operations include equivalence, subset, superset, union, intersection. The application of set operations can result in the expansion of QB sets and the creation of new join sets.

[0022] A join set can be associated with multiple QBs. For example, [{F1-D1, F1-D3, D1-B1}, {Q3, Q5, Q8, Q11}] indicates that an MV based on the join set can potentially rewrite query blocks Q3, Q5, Q8, and Q11.

[0023] For each non-aggregate column on the SELECT list of an automatic MV, two types of QB reference counts can be maintained. A column can have multiple types. These types are based on the properties of the query blocks in the QB set associated with the join set on which the automatic MV is based. One type of column is called type F, which means that the column appears on the filter predicate of the query block in the QB set of the MV. The other type of column is called type J, which means that the column appears on the join predicate of the query block in the QB set of the MV.

[0024] A "partition scheme" is a set of one or more columns of type F (called "partition columns") and a partitioning method (such as list, range, or hash).

[0025] The "reference count" of a column of type F represents the count of those query blocks in which the column appears in the filter predicates of the query blocks. If column C on the SELECT list of an automatic MV based on the join set [{F1-D2,F1-D3},{Q3,Q5,Q8}] appears in the filter predicates of Q3, Q8, and Q11, then the reference count of column C is 3. The higher the reference count, the better. A high reference count for a column means that if the MV is partitioned on this column, the filter predicates in the corresponding query block will benefit from partition pruning. In the above example, query block Q5 will not benefit from partition pruning based on the reference to column C because Q5 does not have a filter predicate on column C. The goal of partitioning is to achieve the highest cumulative reference count that does not exceed the partition count limit.

[0026] The "cumulative reference count" (CRC) of a partition scheme is the sum of the reference counts (RC) of the partition columns in the partition scheme. For example, the cumulative reference count of a partition scheme (x, y, z) = RC(x) + RC(y) + RC(z).

[0027] The "cumulative NDV" (or CNDV) of a partition scheme is the product of the number of distinct values ​​(NDV) of the columns in the partition scheme. The partition count of a partition scheme cannot exceed its CNDV.

[0028] Overview of Partitioning Strategies

[0029] For automatic partitioning, only columns of type F are considered. Columns of type F enable static partition pruning in query blocks rewritten using MV. List partitioning method is used for MV.

[0030] If the partitioning column has an equality filter predicate and the partitioning scheme includes only one partitioning column, then the MV can be partitioned using hash partitioning. For hash partitioning, the desired number of partitions can be explicitly specified. An example of an explicit query statement for specifying the desired number of partitions is "partition by HASH(T1.b1) partitions 16", where T1.b1 refers to column b1 of table T1, and 16 is the desired number of partitions.

[0031] After the automatic MV has been automatically selected by the MV advisor and before the automatic MV is subject to validation by the validation module, a partitioning decision is made for the recommended automatic MV. Thus, the automatically partitioned automatic MV can be validated before being published (or used by a query processor).

[0032] For a single column partitioning scheme, the number of distinct values ​​(NDV) of the column can be used as an approximation for the number of partitions.

[0033] Combinations of a given set of partition columns generate different partitioning schemes. For example, for three columns A, B, and C, all of their combinations (i.e., {A}, {B}, {C}, {A,B}, {A,C}, {B,C}, {A,B,C}) represent different partitioning schemes.

[0034] Generally, more partition columns result in larger CRCs and higher partition counts.

[0035] The automatic partitioning algorithm can identify an upper bound on the number of partitioning columns and an upper bound on the number of partitions (i.e., the "partition count"). A relatively small partition count is not considered optimal because the resulting partitions may be too large to provide much benefit from partition pruning. Users such as database administrators can override the default values ​​for these two upper bounds.

[0036] The goal of the automatic partitioning algorithm can be to select a partitioning scheme that maximizes CRC within the constraints of two upper bounds. A higher CRC means that more query blocks rewritten with MV will benefit from more effective static partition pruning.

[0037] Process Overview

[0038] Figure 1 is a flow chart depicting an example process 100 for automatically partitioning an MV in an embodiment. Process 100 may be performed by a database server having metadata access rights to a database object (eg, table) on which a materialized view is based.

[0039] At block 110, a definition of a materialized view is identified. The definition may have been automatically generated or may have been manually specified (or "user defined"). The definition includes references to tables and columns and optionally includes predicate clauses and / or group-by clauses. Process 100 may be triggered by notification that one or more MVs have just been generated and are therefore candidates for partitioning. Additionally or alternatively, process 100 may be triggered by notification that one or more base tables on which the MVs are based have changed significantly (e.g., a certain number of new rows, deleted rows, and / or modified rows), which requires repartitioning of the MVs.

[0040] At block 120, a plurality of candidate partitioning schemes are identified based on the definition. The candidate partitioning schemes include a set of one or more columns and a partitioning method, such as list partitioning or hash partitioning.

[0041] Block 120 may involve identifying a query block associated with the MV and determining whether there are any columns that appear in a filter predicate on the query block. Such columns are candidates from which candidate partitioning schemes may be identified. For example, if there are two candidate columns that may be used for partitioning, then there may be a total of three candidate partitioning schemes: one candidate partitioning scheme for one of the two columns, one candidate partitioning scheme for the other of the two columns, and one candidate partitioning scheme for both columns together. Thus, the three candidate partitioning schemes correspond to three combinations of sets of two columns.

[0042] At box 130, a partition count query indicating the partition count for each candidate partition scheme is generated. The partition count query can be a SQL query including a SELECT clause and a FROM clause. The partition count query also includes a portion of the definition of the MV. The partition count query may include a grouping set clause as described in more detail herein. The partition count query may include multiple independent SQL queries, each SQL query corresponding to a different partition scheme.

[0043] At block 140, a partition count query is executed that results in the generation of multiple partition counts, each partition count corresponding to a different candidate partition scheme. (In embodiments where multiple partition count queries are generated, block 140 would involve executing each of them.) Block 140 may involve the database server executing a partition count query on metadata for multiple database objects, such as tables. The metadata may include the number of distinct values ​​in a column for each column of the candidate partition scheme. The partition count for the candidate partition scheme indicates the number of partitions that may be generated by partitioning the MV according to the candidate partition scheme.

[0044] At block 150, based on the partition count, one of the candidate partition schemes is selected. For example, the candidate partition scheme with the highest partition count is selected. As another example, the candidate partition scheme with the highest partition count that does not exceed a partition count threshold is selected. As another example, the candidate partition scheme with the highest partition count and that satisfies one or more other criteria is selected.

[0045] Frame 150 can involve considering candidate partition schemes in a particular order, such as candidate partition schemes associated with larger cumulative reference counts. Next, for each candidate partition scheme, check whether one or more criteria are met. After determining that the candidate partition scheme meets one or more criteria, select the candidate partition scheme and ignore any remaining candidate partition schemes. The method can save computer resources (e.g., CPU and computer memory) and time by avoiding analysis of candidate partition schemes that are unlikely to be better than candidate partition schemes that have been considered.

[0046] At block 160, the materialized view is automatically partitioned based on the selected candidate partitioning scheme. The partitioning method of the selected candidate partitioning scheme may be list partitioning or hash partitioning.

[0047] Identifying candidate partitioning schemes

[0048] Given an MV definition, multiple columns of type F are identified. In an embodiment where the MV definition is in SQL, the columns are identified in the SELECT clause of the MV definition. In addition, given the MV definition, a set of query blocks is identified. In an embodiment, the MV definition is based on a join set, which is associated with a QB set (i.e., a set of query blocks). For each identified column, determine whether the column appears in a filter predicate of a query block in the set of query blocks. (In other words, identify columns of type F.) If this determination is affirmative, then update the column data (which may initially be empty or NULL) to identify the column. After checking each identified column in this manner, the column data identifies a set of zero or more columns and their reference counts. If the column data does not identify any columns (e.g., the column data is empty or NULL), then there is no feasible partitioning for the MV.

[0049] If the column data identifies a single column, then multiple partitioning schemes may exist, but they differ only in the partitioning method (e.g., list or hash). Next, the number of distinct values ​​(NDV) in the column is determined, for example, by examining the metadata of the table to which the column belongs. If the value of the NDV for the column is not available, then a query may be initiated in order to calculate the NDV. This NDV may be used as the partition count for the MV unless other criteria are not met as described in more detail herein.

[0050] In a scenario where multiple columns are identified in the column data, multiple combinations from the identified columns are generated. For example, if columns C1 and C2 are identified, the following three combinations are generated: {C1}, {C2}, {C1, C2}. As another example, if columns C1, C2, and C3 are identified, the following seven combinations are generated: {C1}, {C2}, {C3}, {C1, C2}, {C1, C3}, {C2, C3}, {C1, C2, C3}. Each combination corresponds to a different partitioning scheme. Some combinations may correspond to multiple partitioning schemes, each partitioning scheme corresponding to a different partitioning method. For example, partitioning scheme 1 (S1) may include a partitioning column {C1}, where the partitioning method is a list; partitioning scheme 2 (S2) may include a partitioning column {C1}, where the partitioning method is a hash. In an embodiment, hash partitioning is preferred for a partitioning scheme having only one partitioning column and in which all filter predicates on the partitioning column in the underlying query block of the MV are equal.

[0051] In scenarios where the column data identifies multiple columns, the columns can be ordered based on reference counts from highest reference count to lowest reference count. For example, if there are columns A, B, and C, and B has the highest reference count and A has the lowest reference count, then the columns are ordered as follows: B, C, A.

[0052] Partition count query

[0053] As indicated above, a partition count query may be generated and, when executed, may be used to generate partition counts for each of a plurality of candidate partition schemes. In an embodiment, a single partition count query is used to efficiently estimate counts for a plurality of partition schemes.

[0054] The following is an example definition of an unpartitioned MV called MV2, where only Fa, D2.b, and D3.c have reference counts greater than zero:

[0055] CREATE MATERIALIZEDVIEW MV2 ENABLE QUERY REWRITE AS

[0056] SELECT F.k1,D2.b,D3.c,Fa,SUM(Fm)sm,COUNT(Fm)cn

[0057] FROM F,D2,D3

[0058] WHERE F.k2=D2.k2 and F.k3=D3.k3

[0059] GROUP BY F.k1,D2.b,D3.c,Fa;

[0060] In response to identifying MV2, a process (e.g., a database server process responsible for partitioning MV) constructs a partition count query that, when executed, generates a partition count for each combination of partition columns, which in this example are: {Fa}, {D2.b}, {D3.c}, {Fa,D2.b}, {Fa,D3.c}, {D2.b,D3.c}, and {Fa,D2.b,D3.c}. An example of such a partition count query is the following GROUPING SET query:

[0061] SELECT GID,COUNT(*)PartCount

[0062] FROM(SELECT GROUPING_ID(Fa,D2.b,D3.c)GID

[0063] FROM F,D2,D3

[0064] WHERE F.k2=D2.k2 and F.k3=D3.k3

[0065] GROUP BY GROUPING SETS((Fa),(D2.b),(D3.c),(Fa,D2.b),(Fa,D3.c),(D2.b,D3.c),(Fa,D2.b,D3.c))

[0066] GROUP BY GID;

[0067] In this example, the order of the columns specified in GROUPING_ID() is significant, but the order is not significant in the GROUPING SET clause. In this example, GROUPING_ID (GID) represents the grouping scheme. GID is a decimal representation of a bit vector with 0 for columns that are present and 1 for columns that are missing. For GROUPING_ID(Fa, D2.b, D3.c), GSQ1 can return the following 7 rows: [0, 1984], [1, 130], [2, 173], [3, 11], [4, 1889], [5, 130], and [6, 19], where the first number refers to the partition scheme and the second number refers to the partition count. GID 0 (ie, 000) represents the partition scheme {Fa, D2.b, D3.c}, which has a partition count of 1984; GID 1 (ie, 001) represents the partition scheme {Fa, D2.b}, which has a partition count of 130; GID 6 (ie, 110) represents the partition scheme {D3.c}, which has a partition count of 19; and so on.

[0068] In an embodiment, one or more of the tables in the FROM clause of the MV definition may be sampled during the execution of the corresponding partition count query. For example, in the partition count query above, the FROM clause may be replaced with the following clause, "FROM Fsample block(20), D2, D3", which means to access 20% of the blocks of table F during the execution of the query. In the case of sampling one or more tables including the partition column (during the execution of the partition count query), any partition counts generated from the sampled data are estimated values.

[0069] Decreased partition count

[0070] In some cases, the partition count of the partition scheme exceeds an upper bound or threshold. The upper bound is an amount indicating that a partition count exceeding this amount may have a cost that exceeds the benefits of the partition. The cost may be in the form of increased partition maintenance and even query processing. The upper bound may be a default value. Additionally or alternatively, the upper bound may be defined by a database administrator.

[0071] In an embodiment, instead of ignoring a partition scheme whose partition count exceeds an upper bound, it is determined whether the partition count can be reduced. A function may be defined that takes a partition scheme (or a set of partition columns) and the partition count of the partition scheme as input. Accordingly, the function calculates a reduced partition count when possible. In order to achieve a partition count reduction, one or more columns in the partition columns must be candidates for having virtual columns generated for them (as described in more detail herein). The following is an example pseudocode for a function ("ReducedPartCount") for reducing the partition count ("PartCount") of a partition scheme (S), where ndv(C) returns the number of distinct values ​​of column C and DPC is the desired partition count (which may be a default value):

[0072]

[0073]

[0074] In this example code, as discussed further herein, ValidSOIHB(C) returns TRUE if a virtual column can be generated for column C. In addition, a variable prNdv keeps track of the product of the number of distinct values ​​(also referred to as CNDV) for all columns in partition scheme S, while a variable prLgNdv keeps track of the product of the DPCs for all columns (in S) for which a virtual column definition for that column is feasible, and a variable prNoLgNdv keeps track of the product of the number of distinct values ​​for all columns (in S) for which a virtual column definition for that column is not feasible.

[0075] Virtual columns

[0076] If the partition count (whether estimated or not) of a partition scheme containing column C exceeds the upper bound of the partition count, then a virtual column can be defined for column C (e.g., by using a database function), where the virtual column can be used to potentially limit the partition count to a desired number.

[0077] An example of a function that creates a virtual column based on an existing column is SYS_OP_INTERVAL_HIGH_BOUND (or "SOIHB" for short), which takes as input the name of the column, the interval size, and the normalized low value. The function maps each value of the specified column (e.g., "T.col") to another value in order to achieve the desired partition count (DPC), which can be internally set to a small number. For example, values ​​0-99 in T.col are mapped to 0, values ​​100-199 in T.col are mapped to 1, and so on. The value of the interval size can be calculated using the DPC, the normalized high value of the column, and the normalized low value of the column. The equation can be expressed as follows:

[0078] IntervalSize=(NormHighVal(T.col)-NormLowVal(T.col)) / DPC;

[0079] NormHighVal() and NormLowVal() return the normalized highest and lowest values ​​of T.col, respectively. Normalized values ​​or normalization are used for columns with data types other than numeric, for example, a column of data type "date" is converted to a column of data type numeric, enabling calculation of the column's low and high values ​​and interval size. These values ​​can be maintained as optimizer statistics.

[0080] Regardless of how the virtual column may be defined (whether using SYS_OP_INTERVAL_HIGH_BOUND or another function), the virtual column can be used to partition the MV list. Virtual columns are not necessary for hash partitioning, because hash partitioning allows the specification of the number of partitions. This ensures that the partitions generated by the virtual column will not exceed the DPC.

[0081] In an embodiment, the virtual column generator operates on columns with data types of numbers or dates. If the column has a data type of varchar (or variable character), then another computer process can first be used to convert the varchar value to a number and then the virtual column generator operates on these values.

[0082] If one or more virtual columns are generated in order to reduce the partition count of the partition scheme, then the MV definition is updated to reference the one or more virtual columns in order to take into account the declaration of the one or more columns. The following is an example MV definition for MV1 with virtual columns VB3 and VA defined through SOIHB, list partitioning method, and virtual and regular columns:

[0083] CREATE MATERIALIZEDVIEW MV1

[0084] VIRTUAL COLUMNS(

[0085] VB3 AS(SYS_OP_INTERVAL_HIGH_BOUND(D3.B3,27,1),

[0086] VA AS(SYS_OP_INTERVAL_HIGH_BOUND(FA,15,0))

[0087] PARTITION BY LIST(D2.B2,VB3,VA)AUTOMATIC

[0088] (PARTITION P0 VALUES(null,null,null))

[0089] enable query rewrite AS

[0090] SELECT D3.B3,D3.C3,FA,D2.B2,SUM(FC)sm,COUNT(FM)cn

[0091] FROM F,D2,D3

[0092] WHERE F.k3=D3.k3 and F.k2=D2.k2

[0093] GROUP BY D3.B3,D3.C3,D2.B2,FA,D3.B3;

[0094] DETAILED AUTO-PARTITIONING ALGORITHM

[0095] Figure 2A-2B is a flow chart depicting an example process 200 for automatically partitioning a materialized view in an embodiment. Although the blocks of process 200 are depicted and described in a particular order, other orders are possible.

[0096] At block 205, reference counts of columns listed in the MV definition are determined. The columns analyzed may be limited to columns in the SELECT clause of the MV definition and columns found in filter predicates of the query block to which the MV is rewritten.

[0097] Columns with non-zero reference counts are sorted in descending order at block 210. This sorting ensures that columns with the highest chance of generating the most partitions are considered first.

[0098] At block 215 , it is determined whether the limit on the number of partitioning columns is one. (Such a limit may be a default limit and may be modified by a user such as a database administrator.) If so, process 200 proceeds to block 220 ; otherwise, process 200 proceeds to block 235 .

[0099] At block 220, the first / next column is selected from the sorted list. If this is the first iteration of block 220, the selected column has the highest reference count. If this is the second iteration of block 220, the selected column has the second highest reference count or a reference count that is equal to the reference count of the first selected column (if multiple columns have the same reference count).

[0100] At box 225, determine whether the number of distinct values ​​of the selected column is greater than (or equal to) a lower bound and whether one or more other criteria are satisfied. The lower bound may be a default value or may be adjusted by a system (or database) administrator. The lower bound represents the minimum value of a partition, below which the cost of the partition is likely to be greater than any benefit from partition pruning.

[0101] Examples of one or more other criteria being satisfied are whether the number of distinct values ​​of the selected column is less than (or equal to) an upper bound, whether a virtual column can be generated from the selected column, or whether the selected column can be hash partitioned. As described above, if the selected column is the only column of the MV being partitioned and all filter predicates on the selected column in the underlying query block are equal, then the selected column can be hash partitioned.

[0102] The one or more other criteria may be one, two or three of the three criteria. If the one or more other criteria are two or more criteria, then satisfying any of the two or more criteria is sufficient to produce a positive result for block 225.

[0103] If block 225 produces an affirmative result, process 200 proceeds to block 230. Otherwise, process 200 returns to block 220. However, if all columns have been considered and there are no more columns to select, process 200 ends without automatic partitioning.

[0104] At block 230, the selected column becomes a partitioning column of the partitioning scheme of the MV. If block 230 is considered, one or more columns in the sorted list of columns may not have been considered. For example, the column selected in 230 may have been the first column selected from the sorted list and the sorted list may have listed multiple columns. If block 230 is performed, process 200 jumps to block 295.

[0105] At box 235, a set of columns is selected from the top of the sorted list of columns. The number of columns in this set is an upper bound of the number of partitioning columns or less (in which case the number of columns in the sorted list of columns is less than the upper bound). For example, if the upper bound is four and the number of columns in the sorted list is three, then all of the columns in the sorted list are selected. On the other hand, if the upper bound is four and the number of columns in the sorted list is five, then the first four columns in the sorted list are selected.

[0106] At box 240, a set of partitioning schemes is generated based on the set of selected columns. Box 240 involves generating all possible combinations of partitioning columns. If lists and ranges are options, it is possible to have two partitioning schemes with the same partitioning columns but different partitioning methods.

[0107] At block 245, a partition count query is generated that indicates the set of partition schemes. An example of a partition count query is a grouping set query that includes a grouping set clause that identifies each partition scheme in the set of partition schemes.

[0108] At block 250, a partition count query is performed to calculate a partition count for each partition scheme. Block 250 may involve identifying the number of distinct values ​​for each partition column in the partition scheme, and if there are multiple partition columns in the partition scheme, multiplying these numbers to produce a partition count for the partition scheme.

[0109] At block 255, a cumulative reference count for each partition scheme is determined. Block 255 may sometimes be performed before block 250. Block 255 may involve determining a reference count (as defined herein) for each partition column in the partition scheme, and if there are multiple partition columns in the partition scheme, summing or accumulating the reference counts to produce a cumulative reference count for the partition scheme.

[0110] At block 260, the partition schemes are sorted in descending order based on their respective cumulative reference counts. Thus, the partition scheme with the highest cumulative reference count is first, the partition scheme with the second highest cumulative reference count is second, and so on.

[0111] At block 265, a partition scheme is selected from the sorted list of partition schemes. The first iteration of 265 for a particular MV involves selecting the first partition scheme in the sorted list. The second iteration of 265 for a particular MV involves selecting the second partition scheme in the sorted list, and so on.

[0112] At block 270, it is determined whether the partition count of the selected partition scheme is less than a lower bound or greater than an upper bound, which may be a default value. If so, process 200 returns to block 265 to select the next partition scheme; otherwise, process 200 proceeds to block 275. However, if there are no more partition schemes to select, process 200 ends without determining a partition scheme for the MV.

[0113] At block 275, a determination is made as to whether one or more criteria are satisfied. Example criteria include (a) the partition count of the selected partitioning scheme is less than (or equal to) the upper bound and (b) hash partitioning is available for the selected partitioning scheme. If there is only one partitioning column in the partitioning scheme and all filter predicates on the partitioning column in the underlying query block are equal, then hash partitioning is available for the partitioning scheme. If one of these criteria is satisfied, the determination in block 275 is affirmative. If the determination in block 275 is affirmative, then process 200 proceeds to block 280; otherwise, process 200 proceeds to block 285.

[0114] At block 280 , the selected partitioning scheme is identified as the partitioning scheme to be used for automatically partitioning the MV. If block 280 is performed, process 200 jumps to block 295 .

[0115] At block 285, a reduced partition count is calculated for the selected partition scheme.The reduce partition count algorithm described herein may be used to calculate a reduced partition count based on a partition count previously calculated for the selected partition scheme.

[0116] At block 290, it is determined whether the decremented partition count is less than (or equal to) the upper bound. If so, process 200 returns to block 280; otherwise, process 200 returns to block 265 to select the next partition scheme. However, if there are no more partition schemes to select, process 200 ends without determining a partition scheme for the MV.

[0117] At block 295, the MV is automatically partitioned based on the partitioning method of the selected partitioning scheme and based on the partitioning column(s) of the selected partitioning scheme. If one or more partitioning columns are marked for virtual column creation, then these one or more virtual columns are created and used to partition the MV.

[0118] Box 295 may involve first updating the MV definition to include a partitioning clause indicating the partitioning columns and partitioning method of the selected partitioning scheme, and then executing the updated MV definition (e.g., by a database server). An example of an updated MV definition is MV1 provided herein. As in this example, if one or more partitioning columns are marked for virtual column creation (e.g., in a reduced partition count) during the process of identifying a partitioning scheme to be used for automatic partitioning of the MV, the MV definition is also updated to reference the one or more virtual columns.

[0119] Hardware Overview

[0120] According to one embodiment, the technology described herein is implemented by one or more special-purpose computing devices. The special-purpose computer device can be hard-wired to perform the technology, or can include a digital electronic device (such as one or more application-specific integrated circuits (ASICs) or field programmable gate arrays (FPGAs)) that is permanently programmed to perform the technology, or can include one or more general-purpose hardware processors that are programmed to perform the technology according to program instructions in firmware, memory, other storage, or a combination thereof. Such a special-purpose computing device can also combine customized hard-wired logic, ASICs or FPGAs with customized programming to implement the technology. 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 that contains hard-wired and / or programmed logic to implement the technology.

[0121] For example, Figure 3 is a block diagram illustrating a computer system 300 upon which embodiments of the invention may be implemented. Computer system 300 includes a bus 302 or other mechanism for communicating information and a hardware processor 304 coupled with bus 302 for processing information. Hardware processor 304 may be, for example, a general purpose microprocessor.

[0122] Computer system 300 also includes a main memory 306, such as a random access memory (RAM) or other dynamic storage device, coupled to bus 302 for storing information and instructions to be executed by processor 304. Main memory 306 may also be used to store temporary variables or other intermediate information during execution of instructions to be executed by processor 304. Such instructions, when stored in a non-transitory storage medium accessible to processor 304, transform computer system 300 into a special-purpose machine customized to perform the operations specified in the instructions.

[0123] Computer system 300 also includes a read only memory (ROM) 308 or other static storage device coupled to bus 302 for storing static information and instructions for processor 304. A storage device 310, such as a magnetic disk, optical disk, or solid state drive, is provided and coupled to bus 302 for storing information and instructions.

[0124] The computer system 300 may be coupled via the bus 302 to a display 312, such as a cathode ray tube (CRT), for displaying information to a computer user. An input device 314, including alphanumeric and other keys, is coupled to the bus 302 for communicating information and command selections to the processor 304. Another type of user input device is a cursor control 316, such as a mouse, trackball, or cursor direction keys, for communicating information and command selections to the processor 304 and for controlling cursor movement on the display 312. The input device typically has two degrees of freedom in 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.

[0125] Computer system 300 may implement the techniques described herein using custom hardwired logic, one or more ASICs or FPGAs, firmware, and / or program logic that are combined with the computer system to enable computer system 300 or program it as a special purpose machine. According to one embodiment, the techniques herein are performed by computer system 300 in response to processor 304 executing one or more sequences of one or more instructions contained in main memory 306. Such instructions may be read into main memory 306 from another storage medium such as storage device 310. Execution of the sequences of instructions contained in main memory 306 causes processor 304 to perform the process steps described herein. In alternative embodiments, hardwired circuits may be used in place of or in combination with software instructions.

[0126] The term "storage medium" as used herein refers to any non-transitory medium that stores data and / or instructions that cause a machine to operate in a particular manner. Such storage media may include non-volatile media and / or volatile media. Non-volatile media include, for example, optical disks, magnetic disks, or solid-state drives, such as storage device 310. Volatile media include dynamic memory, such as main memory 306. Common forms of storage media include, for example, floppy disks, flexible disks, hard disks, solid-state drives, magnetic tapes or any other magnetic data storage media, CD-ROMs, any other optical data storage media, any physical media with a pattern of holes, RAM, PROMs and EPROMs, FLASH-EPROMs, NVRAMs, any other memory chips or cassettes.

[0127] Storage media are distinct from transmission media but can be used in conjunction with transmission media. Transmission media participate in transmitting information between storage media. For example, transmission media include coaxial cables, copper wires, and optical fibers, including the wires that comprise bus 302. Transmission media can also take the form of acoustic waves or broadcasts, such as those generated during radio wave and infrared data communications.

[0128] One or more sequences of one or more instructions are carried to the processor 304 for execution of various forms involving media. For example, the instructions may initially be carried on a disk or solid-state drive of a remote computer. The remote computer can load the instructions onto its dynamic memory and send the instructions via a telephone line using a modem. The modem local to the computer system 300 can receive data on the telephone line and use an infrared transmitter to convert the data into an infrared signal. An infrared detector can receive the data carried in the infrared signal and a suitable circuit can place the data on the bus 302. The bus 302 carries the data to the main memory 306, from which the processor 304 retrieves the instructions and executes them. The instructions received by the main memory 306 can optionally be stored on the storage device 310 before or after being executed by the processor 304.

[0129] The computer system 300 also includes a communication interface 318 coupled to the bus 302. The communication interface 318 provides a bidirectional data transmission coupled to a network link 320 connected to a local network 322. For example, the communication interface 318 can be an integrated services digital network (ISDN), 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 318 can be a local area network (LAN) card that provides a connection to a compatibility LAN. A wireless link can also be implemented. In any such implementation, the communication interface 318 sends and receives electrical, electromagnetic, or optical signals that carry digital data streams representing various types of information.

[0130] The network link 320 typically provides data communication to other data devices through one or more networks. For example, the network link 320 may provide a connection through a local network 322 to a host computer 324 or data equipment operated by an Internet Service Provider (ISP) 326. The ISP 326 in turn provides data communication through the global packet data communication network now commonly referred to as the "Internet" 328. Both the local network 322 and the Internet 328 use electrical, electromagnetic or optical signals that carry digital data streams. The signals through the various networks and the signals on the network link 320 and through the communication interface 318 that carry the digital data to and from the computer system 300 are example forms of transmission media.

[0131] Computer system 300 is able to send messages and receive data, including program code, through the network(s), network link 320, and communication interface 318. In the Internet example, server 330 may transmit requested code for an application program through Internet 328, ISP 326, local network 322, and communication interface 318.

[0132] The received code may be executed by processor 304 as it is received, and / or stored in storage device 310 or other non-volatile storage for later execution.

[0133] Software Overview

[0134] Figure 4 4 is a block diagram of a basic software system 400 that may be employed to control the operation of computer system 300. Software system 400 and its components, including their connections, relationships, and functions, are intended to be exemplary only, and are not intended to limit the scope 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.

[0135] The software system 400 is provided for directing the operation of the computer system 300. The software system 400 includes a kernel or operating system (OS) 410, which may be stored in system memory (RAM) 306 and on fixed storage (eg, hard disk or flash memory) 310.

[0136] OS 410 manages 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 402A, 402B, 402C, ..., 402N, may be "loaded" (e.g., transferred from fixed storage 310 into memory 306) for execution by system 400. Applications or other software intended for use on computer system 300 may also be stored as a collection of downloadable computer executable instructions, such as instructions for downloading and installing from an Internet location (e.g., a Web server, application store, or other online service).

[0137] The software system 400 includes a graphical user interface (GUI) 415 for receiving user instructions and data in a graphical manner (e.g., "point and click" or "touch gestures"). These inputs are acted upon accordingly by the system 400 according to instructions from the operating system 410 and / or (one or more) applications 402. The GUI 415 also serves to display the results of the operations from the OS 410 and (one or more) application programs 402, and the user can then provide additional input or terminate the session (e.g., log off).

[0138] The OS 410 can execute directly on the bare hardware 420 (e.g., the processor(s) 304) of the computer system 300. Alternatively, a hypervisor or virtual machine monitoring system (VMM) 430 can be interposed between the bare hardware 420 and the OS 410. In this configuration, the VMM 430 acts as a software "buffer" or virtualization layer between the OS 410 and the bare hardware 420 of the computer system 300.

[0139] VMM 430 instantiates and runs one or more virtual machine instances ("clients"). Each client includes a "guest" operating system such as OS 410 and one or more applications designed to execute on the guest operating system such as application(s) 402. VMM 430 provides a virtual operating platform for the guest operating system and manages the execution of the guest operating system.

[0140] In some instances, VMM 430 can allow a guest operating system to run as if it were running directly on bare hardware 420 of computer system 300. In these examples, the same version of the guest operating system that is configured to execute directly on bare hardware 420 can also execute on VMM 430 without modification or reconfiguration. In other words, in some instances, VMM 430 can provide full hardware and CPU virtualization to the guest operating systems.

[0141] In other examples, the guest operating system may be specifically designed or configured for efficiency to execute on VMM 430. In these examples, the guest operating system "knows" that it is executing on a virtual machine supervisory system. In other words, in some examples, VMM 430 may provide paravirtualization to the guest operating system.

[0142] A computer system process includes an allotment of hardware processor time and an allotment of memory (physical and / or virtual) for storing instructions executed by the hardware processor, for storing data generated by the hardware processor in executing instructions, and / or for storing hardware processor state (e.g., contents of registers) between allotments of hardware processor time when the computer system is not running. A computer system process runs under the control of an operating system and may run under the control of other programs executing on the computer system.

[0143] The basic computer hardware and software described above are shown for the purpose of illustrating the underlying computer components that can be used to implement the (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. On the contrary, the (one or more) example embodiments can be implemented in any type of system architecture or processing environment, and those skilled in the art will understand the features and functions that can support the (one or more) example embodiments shown herein based on this disclosure.

[0144] cloud computing

[0145] The term "cloud computing" is used generally 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 allows rapid provisioning and release of resources with minimal effort or interaction by the service provider.

[0146] A cloud computing environment (sometimes referred to as a cloud environment or cloud) can be implemented in a variety of different ways to best suit different needs. For example, in a public cloud environment, the underlying computing infrastructure is owned by an organization that makes cloud services available to other organizations or to the general public. In contrast, a private cloud environment is typically intended to be used only by or within a single organization. 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) tied together by data or application portability.

[0147] In general, the cloud computing model enables some of the responsibilities that would previously have been provided by an organization's own information technology department to be delivered as a service layer within the cloud environment for use by consumers (either within the organization or outside the organization, depending on the public / private nature of the cloud). The exact definition of the components or features within or provided by each cloud service layer may vary depending on the specific implementation, but common examples include: Software as a Service (SaaS), where consumers use software applications running on the cloud infrastructure, while the SaaS provider manages or controls the underlying cloud infrastructure and applications. Platform as a Service (PaaS), where consumers can develop, deploy, or control their own applications using software programming languages ​​and development tools supported by the PaaS provider, while the PaaS provider manages or controls other aspects of the cloud environment (i.e., everything under the runtime execution environment). Infrastructure as a Service (IaaS), where consumers can deploy and run arbitrary software applications, and / or provision processing, storage, networking, and other basic computing resources, while the IaaS provider manages and 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 database management system running on a cloud infrastructure, while the DbaaS provider manages or controls the underlying cloud infrastructure, applications, and servers, including one or more database servers.

[0148] In the foregoing specification, embodiments of the present invention have been described with reference to numerous specific details, which may vary from implementation to implementation. Therefore, the specification and drawings are to be regarded in an illustrative rather than a restrictive sense. The only and exclusive indicator of the scope of the present invention and intended by the applicant to be the scope of the present invention is the literal and equivalent range of the set of claims issued from this application, in the specific form in which such claims are issued, including any subsequent corrections.

Claims

1. A method comprising: Identify the definition of the materialized view; Based on the definition, identifying a plurality of candidate partitioning schemes; generating a query indicating one or more candidate partitioning schemes of the plurality of candidate partitioning schemes; executing the query, wherein executing the query produces one or more partition counts, each partition count corresponding to a different candidate partition scheme among the one or more candidate partition schemes; selecting a candidate partition scheme from among the plurality of candidate partition schemes based on the one or more partition counts; Automatically partitioning the materialized view based on the candidate partitioning scheme; The method is executed by one or more computing devices.

2. The method of claim 1 , wherein identifying the plurality of candidate partitioning schemes comprises: identifying, in a SELECT list of the definition, a plurality of columns having filter predicates in a query block associated with the materialized view; identifying a plurality of combinations of the plurality of columns; Wherein identifying the plurality of candidate partitioning schemes is based on the plurality of combinations.

3. The method according to claim 1, wherein the materialized view is a first materialized view, the method further comprising: identifying a second definition of a second materialized view different from the first materialized view; identifying, based on the second definition, a second plurality of candidate partition schemes, wherein each candidate partition scheme in the second plurality of candidate partition schemes comprises a single column; determining whether the number of distinct values ​​in a column of a particular candidate partition scheme in the second plurality of candidate partition schemes is greater than a lower bound and whether one or more criteria are satisfied; In response to determining that the number of distinct values ​​is greater than the lower bound and the one or more criteria are satisfied, selecting the particular candidate partition scheme from the second plurality of candidate partition schemes; The one or more criteria include: The number of unique values ​​is less than the upper bound, It is possible to define a virtual column from said columns, or Hash partitioning is an effective partitioning method for the said column; The second materialized view is automatically partitioned based on the specific candidate partitioning scheme. 4 . The method of claim 1 , wherein the query is a grouping set query based on the definition of the materialized view and including a grouping set clause.

5. The method according to claim 1, further comprising: determining whether the partition count of the candidate partition scheme is greater than a lower bound and whether one or more first criteria are satisfied; In response to determining that the partition count of the candidate partition scheme is greater than the lower bound and the one or more first criteria are satisfied, the candidate partition scheme is selected.

6. The method according to claim 1, further comprising: Determining whether one or more first criteria are satisfied, wherein the one or more first criteria include: The partition count of the candidate partition scheme is less than an upper bound; or The candidate partition scheme has only one column and all filter predicates associated with the one column in the underlying query block of the materialized view are equal.

7. The method according to claim 1, further comprising: In response to determining that the partition count of the candidate partition scheme is not less than an upper bound, generating a reduced partition count based on the partition count; A determination is made whether the reduced partition count is less than the upper bound.

8. The method according to claim 7, further comprising: In response to determining that the partition count is not less than the upper bound, determining whether the number of distinct values ​​of a column of the candidate partition scheme is greater than a desired partition count; In response to determining that the number of distinct values ​​of the column of the candidate partition scheme is greater than the desired partition count, determining that the column is a candidate for generating a virtual column; The definition of the materialized view is updated to reference the virtual column.

9. The method according to claim 1, further comprising: For each candidate partition scheme among the multiple candidate partition schemes, determine a cumulative reference count of each candidate partition scheme; Before selecting the candidate partition scheme, sorting the plurality of candidate partition schemes based on decreasing cumulative reference counts of the plurality of candidate partition schemes; Wherein selecting the candidate partition scheme is performed without comparing a partition count of one or more other candidate partition schemes among the plurality of candidate partition schemes to an upper bound or a lower bound.

10. The method of claim 1, wherein: The query indicates a sampling of a table indicated in the definition of the materialized view; Each partition count in the plurality of partition counts is an estimated partition count.

11. One or more non-transitory storage media storing instructions that, when executed by one or more computing devices, cause: Identify the definition of the materialized view; Based on the definition, identifying a plurality of candidate partitioning schemes; generating a query indicating one or more candidate partitioning schemes of the plurality of candidate partitioning schemes; executing the query, wherein executing the query produces one or more partition counts, each partition count corresponding to a different candidate partition scheme among the one or more candidate partition schemes; selecting a candidate partition scheme from among the plurality of candidate partition schemes based on the one or more partition counts; The materialized view is automatically partitioned based on the candidate partitioning scheme.

12. The one or more non-transitory storage media of claim 11, wherein identifying the plurality of candidate partition schemes comprises: identifying, in a SELECT list of the definition, a plurality of columns having filter predicates in a query block associated with the materialized view; identifying a plurality of combinations of the plurality of columns; Wherein identifying the plurality of candidate partitioning schemes is based on the plurality of combinations.

13. The one or more non-transitory storage media of claim 11, wherein the materialized view is a first materialized view, wherein the instructions, when executed by the one or more computing devices, further cause: identifying a second definition of a second materialized view different from the first materialized view; identifying, based on the second definition, a second plurality of candidate partition schemes, wherein each candidate partition scheme in the second plurality of candidate partition schemes comprises a single column; determining whether the number of distinct values ​​in a column of a particular candidate partition scheme in the second plurality of candidate partition schemes is greater than a lower bound and whether one or more criteria are satisfied; In response to determining that the number of distinct values ​​is greater than the lower bound and the one or more criteria are satisfied, selecting the particular candidate partition scheme from the second plurality of candidate partition schemes; The one or more criteria include: The number of unique values ​​is less than the upper bound, It is possible to define a virtual column from said columns, or Hash partitioning is an effective partitioning method for the said column; The second materialized view is automatically partitioned based on the specific candidate partitioning scheme.

14. The one or more non-transitory storage media of claim 11, wherein the query is a grouping set query based on the definition of the materialized view and including a grouping set clause.

15. The one or more non-transitory storage media of claim 11, wherein the instructions, when executed by the one or more computing devices, further cause: determining whether the partition count of the candidate partition scheme is greater than a lower bound and whether one or more first criteria are satisfied; In response to determining that the partition count of the candidate partition scheme is greater than the lower bound and the one or more first criteria are satisfied, the candidate partition scheme is selected.

16. The one or more non-transitory storage media of claim 11, wherein the instructions, when executed by the one or more computing devices, further cause: Determining whether one or more first criteria are satisfied, wherein the one or more first criteria include: The partition count of the candidate partition scheme is less than an upper bound; or The candidate partition scheme has only one column and all filter predicates associated with the one column in the underlying query block of the materialized view are equal.

17. The one or more non-transitory storage media of claim 11, wherein the instructions, when executed by the one or more computing devices, further cause: In response to determining that the partition count of the candidate partition scheme is not less than an upper bound, generating a reduced partition count based on the partition count; A determination is made whether the reduced partition count is less than the upper bound.

18. The one or more non-transitory storage media of claim 17, wherein the instructions, when executed by the one or more computing devices, further cause: In response to determining that the partition count is not less than the upper bound, determining whether the number of distinct values ​​of a column of the candidate partition scheme is greater than a desired partition count; In response to determining that the number of distinct values ​​of the column of the candidate partition scheme is greater than the desired partition count, determining that the column is a candidate for generating a virtual column; The definition of the materialized view is updated to reference the virtual column.

19. The one or more non-transitory storage media of claim 11, wherein the instructions, when executed by the one or more computing devices, further cause: For each candidate partition scheme among the multiple candidate partition schemes, determine a cumulative reference count of each candidate partition scheme; Before selecting the candidate partition scheme, sorting the plurality of candidate partition schemes based on decreasing cumulative reference counts of the plurality of candidate partition schemes; Wherein selecting the candidate partition scheme is performed without comparing a partition count of one or more other candidate partition schemes among the plurality of candidate partition schemes to an upper bound or a lower bound.

20. The one or more non-transitory storage media of claim 11, wherein: The query indicates a sampling of a table indicated in the definition of the materialized view; Each partition count in the plurality of partition counts is an estimated partition count.

Images