Automatic linear clustering recommendation of database area maps

Abstract

Techniques are disclosed for automatically and efficiently creating zone maps for database query processing. These techniques include creating a sample dataset to represent an original dataset, building a query workload modeler to characterize a complete workload of queries, building a cluster quality evaluator to evaluate query performance on the dataset with specific clusters on columns, finding a cluster solution by evaluating different applications of the workload to the sample dataset corresponding to different clusters, and determining which columns of the cluster solution can benefit from a zone map.

Description

Technical Field

[0001] The techniques and methods described in this paper relate to the field of database zone maps, particularly the automatic and efficient generation of database zone maps. Background Technology

[0002] The methods described in this section are permissible but not necessarily those previously conceived or employed. Therefore, unless otherwise stated, it should not be assumed that any method described in this section complies with the prior art simply because it is included in this section.

[0003] Region maps are a data access structure that reduces the cost of data access for queries by pre-pruning irrelevant data blocks. However, region maps are most effective when the data in the database tables is clustered in a way consistent with the workload queries and data characteristics, and the region map is constructed for the columns most relevant to the workload. It is helpful to automatically analyze the workload and recommend region maps for optimal performance. Attached Figure Description

[0004] In the accompanying drawings of some embodiments, the same reference numerals are used throughout the figures to refer to corresponding parts:

[0005] Figure 1 Example components of a computer system according to embodiments disclosed herein are illustrated.

[0006] Figure 2 The flowchart illustrates the process of recommending regional maps for a database table according to an embodiment disclosed herein.

[0007] Figure 3 It is a block diagram of a computer system on which embodiments of the methods can be implemented. Detailed Implementation

[0008] In the following description, for illustrative purposes, numerous specific details are set forth to provide a thorough understanding of the method. However, it will be apparent, however, that the method can be practiced without these specific details. In other instances, structures and apparatus are shown in block diagram form to avoid unnecessarily obscuring the method.

[0009] General Overview

[0010] Techniques for automatically and efficiently creating region maps for database query processing are disclosed. These techniques include creating sample datasets to represent the original dataset, building query workload modelers to characterize the complete workload of queries, building clustering quality evaluators to evaluate query performance on datasets with specific clusters on columns, finding clustering solutions by evaluating different applications of the workload to sample datasets corresponding to different clusters, and determining which columns of the clustering solutions can benefit from the region map.

[0011] A database may include multiple tables, and the original dataset can be obtained from one of those tables or by joining some of those tables. A powerful approach to identifying clustering solutions and constructing region maps is to sort the original dataset on different candidate column sets and run a full workload on the resulting sorted data for evaluation. However, in practice, for large datasets, a single sort can take hours. Therefore, the technique disclosed in this application introduces a clustering quality evaluator that takes a sample dataset created from the original dataset. The evaluator can sort the sample dataset on candidate column sets and apply the resulting column clustering to the original dataset, thereby extrapolating performance on the sample dataset to performance on the original dataset. Furthermore, even running a full workload containing numerous queries on the sample dataset can still be very expensive, especially when there are many candidate clusters to search. To address this issue, the disclosed technique introduces a query workload modeler that builds a statistical model for the queries. By replacing the original dataset with the sample dataset and the full workload with the query workload modeler, it takes significantly less time to compare different clusters and determine the appropriate region map to create.

[0012] Regional map

[0013] Region maps allow databases to prune blocks of data containing rows that are irrelevant to query predicates (e.g., the "equals" operator in the WHERE clause of an SQL statement) based on aggregation information, without actually accessing those blocks. To construct a region map, the entire table is divided into groups of rows of a certain size, called regions. Then, for each column of interest, the minimum and maximum values ​​for each region are stored in a data structure. For equality or range predicates in a query, a region can be ignored if it does not overlap with the minimum-to-maximum range of other regions. The data structure containing the minimum and maximum values ​​for each region is called the region map.

[0014] Region maps are best suited for equality and range predicate queries, and when values ​​in a region are concentrated in a small subset of the column range (such as when sorting the column). For example, for a database column called "State" representing the states of the United States, when sorting the column, the minimum and maximum values ​​for the first region could be "AK" and "AZ," followed by "CA" and "DE" for the second region, and so on. For a query with an equality predicate: State = 'CA', when a region map exists on the column, the database can use that region map to determine that it only needs to access the second region to access all relevant rows, and all other data blocks can be pruned without accessing them.

[0015] However, the proper functioning of a region map does not require a complete sorting of the data. As long as the values ​​within a region are within a specific range, it is unnecessary to sort them within the region. To make this distinction, the term "clustering" is often used in the context of region maps to identify columns within which all values ​​fall within a certain minimum and maximum value. Currently, most databases perform clustering by sorting columns, so there is no real distinction between sorted / sorting and clustered / clustering. In this application, these two terms are used interchangeably.

[0016] Linear clustering

[0017] Most database tables have multiple columns that are important for queries, so sorting a single column is often insufficient. One option to address this is to linearly cluster the table across multiple columns. Given a linear clustering Linear(TA,TB,TC) on columns A, B, and C of table T, the database will perform the following: first, sort the entire table on column A; then, sort the keys associated with the values ​​in column A on column B; and finally, sort the associations between columns A and B on column C. In this case, the system can expect the best performance for queries on column A, followed by column B, and then column C. For example, for a database table containing two columns, "State" and "Zip Code," with the following three rows: (TX,78712), (CA,94404), (CA,94401), the linear sort order would be: (CA,94401), (CA,94404), (TX,78712). This is because the rows are first sorted by state, and the two related rows (where State = 'CA') are sorted by zip code.

[0018] General methods

[0019] In some embodiments, for a database workload, i.e., a database instance populated with data and a set of representative queries on the data, the system identifies which columns of the database tables should be clustered on and, given the identified clusters, which columns will benefit from the area map.

[0020] Figure 1 Example components of a computer system according to embodiments disclosed herein are illustrated. In some embodiments, the database management system 100 includes a query workload modeler instruction 102, a clustering quality evaluator instruction 104, a solution search instruction 106, and a region map recommendation instruction 108. In other embodiments, the system 100 may have more or fewer components.

[0021] In some embodiments, query workload modeler instruction 102 enables column pruning and workload model creation. Column pruning involves analyzing queries in the workload to calculate weights for table columns based on how they are used in the query, and identifying candidate columns for clustering based on these weights. Workload model creation involves creating a probabilistic model for the workload query. This model includes information about the importance of each column and additional query predicate characteristics, such as selectivity, as discussed further below.

[0022] In some embodiments, clustering quality evaluator instruction 104 enables the extraction of a sample of rows, including candidate columns, from the original dataset. Clustering quality evaluator instruction 104 also enables the construction of a clustering quality evaluator to assess the quality of clusters in terms of query performance, the execution of the clustering quality evaluator on a sample set of rows clustered using a specific cluster to obtain a quality score for that specific cluster, and the extrapolation of the quality score of the complete set of rows in the original dataset.

[0023] In some embodiments, the solution search instruction 106 enables the use of a greedy algorithm to search the space of possible clusters to find the optimal cluster as the clustering solution. The search includes performing a clustering quality evaluator on a set of row samples clustered for each cluster in the used space.

[0024] In some embodiments, the region map recommendation instruction 108 enables the identification of columns from candidate columns for which region maps are created. Given a clustering pattern, query performance would significantly degrade without a region map.

[0025] Column trimming

[0026] In some embodiments, one factor to consider when deciding whether clustering columns is useful is column importance, which indicates how frequently a column is referenced in a query. cOnly columns that constitute a significant portion of the query predicate should be included in the clustering solution. Therefore, columns that do not account for at least a certain percentage of queries in the workload will be removed for further consideration. For example, that percentage could be 10%.

[0027] In some embodiments, weights are calculated for columns that have not yet been pruned, which may depend on the specific database schema used. Another factor to consider when deciding whether clustering on columns is useful is query selectivity. It is best to cluster on columns where queries are highly selective, i.e., when query predicates reference a relatively small number of values ​​in the column. Column weights can be based on query selectivity. For example, a column weight can indicate how likely that column is the most selective column in a query. Some databases generate this information when generating query plans. Alternatively, weights can be calculated based on a random sample of database rows according to the percentage of queries with the most selective column in the workload. In other embodiments, column weights can be based solely on column importance. For example, a column weight can simply be the percentage of queries in the workload that have predicates on that column. Weights can be normalized to sum to 1 and treated as a probability distribution.

[0028] Workload model creation

[0029] In some embodiments, another factor to consider when determining whether clustering on a column is useful is the number of OR terms in the query on that column. When the query on the column is a union of a large number of equality predicates that require random access to the database, area maps are generally not very useful, even if the data is sorted.

[0030] In some embodiments, a heterogeneity value is calculated for a column regarding a query predicate, defined as the frequency distribution of the number of distinct values ​​(NDV) observed in a single query predicate on that column. For example, the NDV for the query predicate "Where state='CA' or state='TX' or state='WA'" is 3. The heterogeneity value is a characteristic of the query, not a characteristic of the data. Typically, this value can be extracted from the query text or query plan. Formally, heterogeneity h... c Let column c represent a pair. in These are the different values ​​of NDV observed in predicate i of this column. It is to observe the normalized frequency of predicate values.

[0031] Another factor to consider when deciding whether clustering a column is useful is its relationship to other columns. This relationship affects the quality of the clustering. For example, the columns "Zip Code" and "State" are not statistically independent. When data is clustered on "Zip Code," it is automatically clustered on "State." Therefore, clustering on one column (Zip Code) can generate a useful regional map on both columns (Zip Code and State) without specifically clustering on the State. As another example, the columns "State" and "Name" may be statistically independent. Therefore, clustering on only one of these columns is insufficient. Furthermore, since names may be nearly unique, linear clustering on (Name, State) will essentially prevent "State" from clustering. In contrast, clustering on (State, Name) may offer better performance. Using clustering to construct regional maps (including the minimum and maximum values ​​for each region) essentially captures the relationships between columns.

[0032] In some embodiments, the workload is therefore modeled as a set of triples through the effect of column queries: Q = (m c ,w c ,h c ),c=1...n c , where n c It is the number of columns of interest, m c It is the name of column c, w c It is its weight, h c This is the aggregation heterogeneity value of the predicates on this column. The workload model for triple sets can also be viewed as a simplified probabilistic model of how queries are generated based on a dataset. In each step, a database column is selected, where column c has a probability of being selected as w. c Next, a query will be created for the selected columns using a certain number of predicates. The number of predicates follows a certain order of h. c Distribution, making generation The probability of the predicate value is All distinct values ​​in a column are equally likely to be selected as predicate values.

[0033] Clustering quality evaluator

[0034] In some embodiments, a sample of specific rows from the candidate columns obtained from the original dataset is then analyzed to compute the optimal cluster, and regional map columns are also identified. Rows in the sample dataset can be randomly selected using a sampling factor (such as 0.1% or 0.2%). The sampling factor can be selected experimentally such that the minimum and maximum values ​​observed in the regions of the sample dataset are quite close to the actual minimum and maximum values ​​(e.g., more than 10% difference in less than 10% of trials).

[0035] In some embodiments, the quality of the clustering solution is estimated as the expected (average) number of regions accessed for a query randomly selected from the workload. The estimation is performed against the original dataset. The results can then be extended to estimate the clustering quality of a sample dataset using some simplifying assumptions.

[0036] Clustering quality of the original dataset

[0037] In some embodiments, for an original dataset having at least one set of columns on which rows have been clustered and region maps have been constructed, the estimation problem can be formalized as follows. Let A Q It is a random variable representing the number of table accesses during the execution of workload Q. The goal is to calculate E[A]. Q That is, given a query randomly generated from Q, the expected number of visits. As mentioned above, the workload Q is modeled as a probability distribution on the columns, where the query probability on each column is related to its weight w. c Proportional. Therefore, E[A] Q ] can be calculated as:

[0038] E[A Q ]=∑w c E[A c (1)

[0039] c∈Q

[0040] Here E[A] c The value represents the expected number of region visits for the query on column c. Next, let Z be the set of regions, assuming that all candidate columns exist on a region map. For region z... j ∈Z, let l jc This is an indicator function that indicates a random query on column c (from the workload) will access range z. j The event. The goal is to calculate E. therefore,

[0041] E[A c ]=∑E[l jc (2)

[0042] Z j ∈Z

[0043] The expected number of visits to a single region can then be calculated, and this expected number can be summed across all regions to calculate the expected number of visits to a region. Since the expected number of visits to a single region is always less than 1, the expected number of visits to a region is essentially the probability that the region is visited.

[0044] For a single region z j ∈Z, where the minimum and maximum values ​​of column c are known, and the region is... The minimum and maximum values ​​can be written as: and Next, we define the function NDV distance V. c The function (x, y) takes two distinct values ​​x and y from column c and returns the number of distinct values ​​between x and y in sorted order. For example, for a column s containing all 50 states, V c ('CA', 'FL') = 5, including the following state sets: 'CA', 'CO', 'CT', 'DE', and 'FL'. This function returns only the number of distinct values ​​between two given values, unaffected by the frequency of each value. Then E[l jc ]=P[l jc =1] is calculated as follows:

[0045]

[0046] In the above formula, NDVc represents the actual NDV of column c. Furthermore, the sum on the right-hand side is calculated according to its corresponding probability. All possible predicate values ​​for weighting The sum. Specifically, the query will access region z. j The probability is 1 minus the number of queries that return no results. The probability that a key appears in the region. The probability that a randomly selected key does not appear in the region is given by the following formula: Assuming the keys are chosen randomly and all keys have an equal probability of being chosen, then this value is increased to... The power of gives all random choices The probability that none of the keys appear in this region.

[0047] Equation (3) represents the expected number of visits to a single region for a randomly generated query on a randomly selected column. To calculate the expected total number of regions visited for a random query, Equation (3) can be used to calculate the expected number of visits for each region per column. Then, Equation (2) is used to sum these expected numbers for all regions to calculate E[A]. c ], which is the expected total number of regions accessed for each column. Then, the weighted sum of all columns can be calculated using equation (1) to calculate the expected number of regions accessed from a column randomly selected from the workload.

[0048] Clustering quality expansion of a given sample dataset

[0049] In some embodiments, in order to estimate E[A] based on a sample dataset of the original dataset Q The size of each region can be reduced based on a sampling percentage. For S ZGiven the original table region size and the sampling percentage p, we set the region size on the sample to... Reducing the region size in this way actually provides us with the endpoints of each region. and An unbiased estimate.

[0050] In some embodiments, by a simplified assumption, equation (3) Estimates can be made based on a sample dataset. These assumptions include only seeing k*NDV in the sample dataset. c There are 3 distinct keys, where k is a constant fraction across regions. Then, the estimate of this term based on the sample dataset is an unbiased estimate of this term for the entire dataset, since the k terms are canceled out. This can be done as follows:

[0051]

[0052] The left-hand side terms are those observed in the samples (although k is unknown), while the right-hand side terms are the basic facts. The above assumptions imply that missing keys in the sample dataset are distributed across the entire region according to the proportion of the region's sample NDV. Based on these assumptions, equations (3), (2), and (1) can be used with the sample dataset to estimate the quality of clustering.

[0053] In some embodiments, another assumption is made that the heterogeneity of column predicates can be represented by the average number of terms in the predicate that are combined with operators, rather than by a distribution. This is a reasonable assumption when predicate heterogeneity is concentrated on a single value. A related assumption is that all queries on a given column have exactly one predicate. Equations (3), (2), and (1) can be combined into a single equation:

[0054] E[A Q ]=∑ c∈Q w c E[A c (4)

[0055] E[A c It can be approximated as:

[0056]

[0057] Equation (4) can be written as:

[0058]

[0059] The key change between this equation and equation (3) is that instead of summing all possible values ​​of NDV in the query predicate, only the mean is used.

[0060] In some embodiments, the rapid evaluation of equation (6) can be facilitated by the efficient execution of the following two steps. The first step is to find the minimum and maximum values ​​of each (reduced) region in each column. and This can be achieved efficiently using the SQL row_number() function. For example, to find the minimum and maximum values ​​of all regions in column $col based on the clustering column $cluster_cols, you can execute the following SQL query:

[0061] with SAMPLE_TABLE_rn as(

[0062] Select row_number()over(order by$cluster_cols)rn,SAMPLE_TABLE.*from

[0063] SAMPLE_TABLE )

[0065] Select min($col),max($col)from SAMPLE_TABLE_rngroup bytrunc((rn-

[0066] 1) / $zonemap_size);

[0067] The database's query optimizer ensures that the query runs quickly and efficiently. The second step is to find the number of distinct values ​​between any two given values ​​(inclusive) in any column of the sample dataset. This can be accomplished either by creating a temporary table with distinct values ​​for all columns arranged in sorted order or by creating a temporary index on the sample dataset. Since this step is performed on the sample dataset and only needs to be done once as preprocessing (the table can then be reused for all calls to this function), it is relatively inexpensive.

[0068] Solution search algorithm

[0069] In some embodiments, a greedy method can be used to find the clustering solution from which to construct the region map. At each time step, the linear clusters of the previous time step are expanded by adding an additional column to generate a linear cluster with the lowest expected number of region visits. The greedy method can be described in pseudocode as follows:

[0070] Input: S: has columns C = c 1,...,NC samples

[0071] τ: Maximum number of cluster columns allowed (default = 4)

[0072] Δ: The column contains the minimum percentage improvement required (default = 5%).

[0073] Output: A pair soln = (soln._1, solu._2), where soln._1 is a list of clustering columns and soln._2 is the clustering quality.

[0074] initialization:

[0075] 1. Set up a candidate solution queue

[0076] 2. Set baseline_soln ← Expected_Num_Accesses(S, rowid)

[0077] 3 will be against Add to U

[0078] cycle:

[0079] 4 do

[0080] 5. Set cur_soln ← u∈U, where u._2 is the minimum value.

[0081] 6. Set cur_quality to cur_soln._2

[0082] 7. Set cur_clus_cols ← cur_soln._1

[0083] 8. Set new_sol_found ← False

[0084] 9 for c∈C-cur_clus_cols do

[0085] 10. Set new_cand_cols ← cur_clus_cols.append(c)

[0086] 11. Set new_clus_quality ← Expected_Num_Accesses(S, new_cand_cols)

[0087] 12Set impr←(cur_quality-new_clus_quality) / cur_quality

[0088] 13 if impr>Δthen

[0089] 14. Add (new_cand_cols, new_clus_quality) to U

[0090] 15. Set new_soln_found to True.

[0091] 16while len(curr_soln.cols)<τand new_soln_found

[0092] result:

[0093] 17. Set soln ← u ∈ U, where u._2 is the minimum value.

[0094] 18 Returns to solen

[0095] In some embodiments, the function Expected_Num_Accesses(Sample, Candidate Cols) clusters the sample and the set of candidate columns (starting with "rowid" to represent pseudo-columns) as input and returns E[A] Q [ ], that is, the expected number of regions accessed by running a workload on a sample dataset using linear clustering based on the candidate column set applied by equation (6). When a new column c is added to the candidate clustering solution, at least and This may change accordingly. In addition to the input parameters, the function also has the following information related to its available workload:

[0096] 1. The weight w attached to each column c .

[0097] 2. The mean NDV of the predicates in this column.

[0098] In some embodiments, the execution of the greedy method described above produces a clustering solution involving a set of candidate columns and a desired number of region maps accessed by a randomly selected query given the clustering solution, assuming that region maps exist for all candidate columns.

[0099] Regional map column labels

[0100] In some embodiments, the list of candidate columns is pruned, wherein for E[A] Q Columns without significant impact are removed. The expected number of region accesses E[A] for queries on column c when a region map exists. c The result is given by equation (5). When there is no region map on the column, the query for that column will access all regions, therefore E[A c ] = N Z That is, the number of regions in the table, which can be used as a region map to determine the column E[A] Q The baseline of the impact.

[0101] In some embodiments, the function Expected_Num_Accesses returns a vector. Instead of E[A Q ], where the c-th element of the vector is E[A c ]. This is the input to equation (4). Then, the effect of not creating a region map on column c can be calculated as follows: E[A] c Replace ] with N Z And recalculate E[A] using equation (4). Q The recalculated value is represented as... The impact of the area map on column c can then be calculated as follows:

[0102]

[0103] Impact c Therefore, this represents the percentage increase in total area map visits when no area map is created on c. A final list of columns whose area map impact exceeds a specific threshold is selected, and area maps are created only for that final list of columns. In some embodiments, the impact threshold is set to 5%.

[0104] Example processing

[0105] Figure 2 The flowchart illustrates the process of recommending regional maps for a database table according to an embodiment disclosed herein.

[0106] At box 202, the system builds a workload model for a database workload that includes a set of queries against a set of tables. The set of tables comprises one or more ranges of rows. The workload model includes one or more feature values ​​based on the set of queries for each column of the set of tables.

[0107] In some embodiments, one or more feature values ​​of a column including an importance value indicate how frequently the group of queries references that column or how selectively the group of queries selects that column. In other embodiments, one or more feature values ​​of a column including a heterogeneity value indicate how many predicate values ​​the group of queries has on that column on a per-query basis.

[0108] At box 204, the system constructs a sample dataset from the group table. In some embodiments, the construction includes sampling rows in the group table using a sampling factor.

[0109] At box 206, the system determines a column clustering solution based on a workload model. This solution includes a list of columns for the group of tables and a corresponding score indicating the performance of the group of queries on a sample dataset that implements the column clustering solution. In some embodiments, the column clustering solution is a linear column clustering.

[0110] In some embodiments, the system selects a set of columns from the columns of the group table by applying a threshold to the importance value of each column, and determines the column clustering solution only from that set of columns.

[0111] In some embodiments, determining a column clustering solution includes greedily constructing a column clustering solution by identifying one or more columns to be clustered in each iteration.

[0112] In some embodiments, determining the column clustering solution includes calculating, in each iteration, a score corresponding to adding each candidate column to the current column cluster to form an enhanced column cluster based on the workload model. This score is related to the number of times the sample dataset implementing the enhanced column clustering is accessed.

[0113] In some embodiments, when further determining the column clustering solution, the system identifies a specific enhanced column cluster corresponding to the highest score among all enhanced column clusters and calculates the difference between the score corresponding to the current column cluster and the highest score. When the difference is greater than a threshold, the system identifies the specific enhanced column cluster as the column clustering solution; when the difference is not greater than the threshold, the system starts another iteration.

[0114] In some embodiments, determining the column clustering solution includes calculating the number of accesses to the sample dataset based on the number of distinct values ​​for each column in the region map or column list.

[0115] At box 208, the system uses the column clustering solution to cluster the table.

[0116] At box 210, the system creates a region map for at least one column in the column list of the group table.

[0117] In some embodiments, the system also calculates an impact value for each column in the column list, which indicates a reduction in the performance of the set of queries on the sample dataset in the absence of a region map for that column, and creates a region map for at least one column, which excludes columns with impact values ​​below a threshold.

[0118] In some embodiments, calculating the impact value of a column includes calculating the difference between the first number of visits to a sample dataset that achieves a column clustering solution based on a workload model without a region map of the column and the second number of visits to the sample dataset with a region map of the column.

[0119] Hardware Overview

[0120] According to one embodiment, the techniques described herein are implemented by one or more dedicated computing devices. The dedicated computing device may be hardwired to execute these techniques, or may include digital electronic devices such as one or more application-specific integrated circuits (ASICs) or field-programmable gate arrays (FPGAs) persistently programmed to execute these techniques, or may include one or more general-purpose hardware processors programmed to execute these techniques according to program instructions in firmware, memory, other storage devices, or a combination thereof. Such a dedicated computing device may also combine custom hardwired logic, ASICs, or FPGAs with custom programming to implement these techniques. The dedicated computing device may be a desktop computer system, a portable computer system, a handheld device, a networking device, or any other device that combines hardwired and / or program logic to implement these techniques.

[0121] For example, Figure 3 This is a block diagram of a computer system 300 on which embodiments of the present method may be implemented. The computer system 300 includes a bus 302 or other communication mechanism for transmitting information, and a hardware processor 304 coupled to the bus 302 to process information. The hardware processor 304 may be, for example, a general-purpose microprocessor.

[0122] Computer system 300 also includes main memory 306, such as 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 can also be used to store temporary variables or other intermediate information during the execution of instructions executed by processor 304. When stored in non-transitory storage media accessible to processor 304, these instructions make computer system 300 a dedicated 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. Storage device 310 (such as a disk or optical disk) is provided and coupled to bus 302 for storing information and instructions.

[0124] Computer system 300 can be coupled to display 312 (such as a cathode ray tube (CRT)) via bus 302 for displaying information to the computer user. Input device 314, including alphanumeric keys and other keys, is coupled to bus 302 for transmitting information and command selections to processor 304. Another type of user input device is cursor control 316 (such as a mouse, trackball, or arrow keys) for transmitting directional information and command selections to processor 304 and for controlling cursor movement on display 312. 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), which facilitates the device's position in a specified 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 (which, in conjunction with the computer system, enable or program the computer system 300 as a special-purpose machine). According to one embodiment, computer system 300 performs the techniques described herein in response to processor 304 executing one or more sequences of one or more instructions contained in main memory 306. These instructions may be read into main memory 306 from another storage medium (such as storage device 310). Execution of the sequence of instructions contained in main memory 306 causes processor 304 to perform the processing steps described herein. In alternative embodiments, hardwired circuitry may be used instead of or in combination with software instructions.

[0126] As used herein, the term "storage medium" refers to any non-transitory medium that stores data and / or instructions that enable a machine to operate in a particular manner. Such storage media can include non-volatile media and / or volatile media. Non-volatile media include, for example, optical discs or magnetic disks, 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 tape or any other magnetic data storage media, CD-ROMs, any other optical data storage media, any physical media with a perforated pattern, RAM, PROMs and EPROMs, FLASH-EPROMs, NVRAMs, any other memory chips, or magnetic tape cassettes.

[0127] Storage media differ from transmission media but can be used in conjunction with them. Transmission media participate in the transfer of information between storage media. For example, transmission media include coaxial cables, copper wires, and optical fibers, including conductors containing bus 302. Transmission media can also take the form of sound waves or light waves, such as those generated during radio wave and infrared data communication.

[0128] Various forms of media can be used to transfer one or more sequences of one or more instructions to processor 304 for execution. For example, instructions may initially be carried on a disk or solid-state drive of a remote computer. The remote computer may load the instructions into its dynamic memory and transmit them over a telephone line using a modem. A modem local to computer system 300 may receive data over the telephone line and convert the data into an infrared signal using an infrared transmitter. An infrared detector may receive the data carried in the infrared signal, and appropriate circuitry may place the data on bus 302. Bus 302 transfers the data to main memory 306, from which processor 304 retrieves and executes the instructions. Instructions received by main memory 306 may optionally be stored on storage device 310 before or after execution by processor 304.

[0129] Computer system 300 also includes a communication interface 318 coupled to bus 302. Communication interface 318 provides bidirectional data communication coupled to network link 320, which is connected to local network 322. For example, communication interface 318 may be an Integrated Services Digital Network (ISDN) card, a cable modem, a satellite modem, or a modem providing data communication connectivity with a corresponding type of telephone line. As another example, communication interface 318 may be a Local Area Network (LAN) card to provide data communication connectivity with a compatible LAN. Wireless links may also be implemented. In any such implementation, communication interface 318 transmits and receives electrical, electromagnetic, or optical signals carrying streams of digital data representing various types of information.

[0130] Network link 320 typically provides data communication to other data devices via one or more networks. For example, network link 320 can provide a connection via local network 322 to host computer 324 or to data devices operated by Internet Service Provider (ISP) 326. ISP 326 then provides data communication services via a global packet data communication network (now commonly referred to as the "Internet" 328). Both local network 322 and Internet 328 use electrical, electromagnetic, or optical signals carrying digital data streams. Signals through various networks, as well as signals on network link 320 and through communication interface 318 (which carries digital data to and from computer system 300), are example forms of transmission media.

[0131] Computer system 300 can send messages and receive data, including program code, through one or more networks, network links 320, and communication interfaces 318. In the Internet example, server 330 can send requested code to the application through the Internet 328, ISP 326, local network 322, and communication interface 318.

[0132] The received code can be executed by processor 304 upon receipt and / or stored in storage device 310 or other non-volatile memory for later execution.

[0133] In the foregoing description, embodiments of the method have been described with reference to numerous specific details, which may vary depending on the implementation. Therefore, the description and drawings should be considered illustrative rather than restrictive. The scope of the method, and what the applicant intends to be the sole and exclusive indicator of its scope, is the literal and equivalent scope of the set of claims published from this application in the specific form of such claims, including any subsequent amendments.

[0134] Overview of cloud computing

[0135] The techniques described herein are implemented using one or more processing solutions, examples of which include distributed systems, clustering computing systems, and cloud computing systems. In an embodiment, the database management system 100 is part of a cloud computing system. The cloud computing system implements one or more of the following: cloud storage, cloud processing, cloud communication, and any other type of cloud computing service. Furthermore, the cloud computing system can operate under a pay-as-you-go model, a fixed subscription model, etc. In this embodiment, any part (or all) of the functionality attributable to the database management system 100 or other entities within this description can be controlled via an interface exposed at the cloud computing system.

[0136] In the foregoing description, embodiments of the method have been described with reference to numerous specific details, which may vary depending on the implementation. Therefore, the description and drawings should be considered illustrative rather than restrictive. The scope of the method, and what the applicant intends to be the sole and exclusive indicator of its scope, is the literal and equivalent scope of the set of claims published from this application in the specific form of such claims, including any subsequent amendments.

Claims

1. A computer-implemented method, comprising: Build a probabilistic workload model for a database workload that includes a set of queries against a set of tables. The probabilistic workload model includes one or more feature values ​​and probabilities for each column of the set of tables based on the set of queries. The set of tables includes one or more ranges of rows; Construct a sample dataset of rows from the set of tables; A probabilistic workload model is used to select column clustering solutions based on corresponding scores, which indicate the execution performance of the set of queries based on a region map on a sample dataset that implements the column clustering solutions. The set of tables is clustered using the column clustering solution; Create a region map for at least one column in the column list of the set of tables; The database management system executes queries, which includes pruning a set of rows that are irrelevant to the query based on a regional map; as well as The influence value of each column in the column list is calculated by measuring the difference between the first number of visits to the sample dataset that achieves the column clustering solution based on the probabilistic workload model without a regional map and the second number of visits to the sample dataset with a regional map.

2. The computer-implemented method of claim 1, wherein the one or more feature values ​​of the column include importance values, the importance values ​​indicating how frequently the set of queries references the column or how selectively the set of queries selects the column.

3. The computer-implemented method as described in claim 2, further comprising: A set of columns is determined from the columns of the set of tables by applying a threshold to the importance value of each column. The selection is performed only from the set of columns.

4. The computer-implemented method of claim 1, wherein the one or more feature values ​​of the column include heterogeneity values, the heterogeneity values ​​indicating how many predicate values ​​the set of queries has on the column on a per-query basis.

5. The computer-implemented method of claim 1, wherein the construction includes sampling rows in the set of tables using a sampling factor.

6. The computer-implemented method as described in claim 1, wherein the column clustering solution is linear column clustering.

7. The computer-implemented method of claim 1, wherein the selection comprises greedily constructing a column clustering solution by identifying one or more columns to be clustered in each iteration.

8. The computer-implemented method of claim 1, wherein the selection includes, in each iteration, calculating a score based on a probabilistic workload model corresponding to adding each candidate column to the current column cluster to form an enhanced column cluster, the score being related to the number of accesses to the sample dataset that implements the enhanced column cluster.

9. The computer-implemented method of claim 8, wherein the selection includes: Identify the specific enhanced column cluster that corresponds to the highest score among all enhanced column clusters; Calculate the difference between the score corresponding to the current column cluster and the highest score; When the difference is greater than the threshold, the specific enhanced column clustering is identified as the column clustering solution; as well as When the difference is not greater than the threshold, another iteration begins.

10. The computer-implemented method of claim 1, wherein the selection includes calculating the number of accesses to the sample dataset based on the number of distinct values ​​in each column of the region map or the column list.

11. A system for recommending regional maps for database tables, comprising: Processor; and A memory having instructions stored thereon, which, when executed by a processor, cause the processor to implement the computer-implemented method as described in any one of claims 1 to 10.

12. A non-transitory storage medium having instructions stored thereon, which, when executed by a processor, cause the processor to implement the computer-implemented method as described in any one of claims 1 to 10.

13. A computer program product comprising instructions that, when executed by a processor, cause the processor to implement the computer-implemented method as claimed in any one of claims 1 to 10.

Images