Automatic interleaving cluster recommendations for database region mapping

Abstract

The computer measures, for each column of the plurality of rows, a respective frequency of statements filtering the column in a workload of database statements, individually a respective different value count (CDV) in each statement for filtering the column, a respective frequency of each of the CDVs used to filter the column across all database statements and a respective range of values of the column for each of a number of storage areas. A respective efficiency is measured for each of a number of different interleaving ranks (ILs). Each IL uses a respective different subset of columns. Each IL is based on a portion of each of the values for each row in a sampled subset of rows in each column of a subset of columns of IL. The efficiency measurement is based on the frequency of the statements, the range of values of the columns of each storage area, and the frequency of the CDV.

Description

Technical Field

[0001] The present invention relates to accelerated filtering through database statements. This is an interleaved sorting technique for automatically generating an optimal mapping from table columns to value ranges in each column for multiple storage areas containing database blocks. Background Art

[0002] A zone map is a data access structure that reduces the cost of data access for a query by pruning irrelevant data blocks. Zone maps are most effective when data in database tables are clustered together in a way that is consistent with the query workload and the data features and zone maps are constructed for the columns that are most relevant to the workload. It would be helpful to automatically analyze the workload and recommend constructing zone maps for optimality.

[0003] Unsorted rows in database tables can result in suboptimal data locality, suboptimal region mapping, and suboptimal query execution latency. The benefits of state-of-the-art approaches to improving data locality can be limited due to data dependencies between table columns. In some cases, such data dependencies can be difficult to discover, and suboptimal data locality can be difficult to diagnose. When data locality is reduced, filter-intensive activities such as data mining, data science, reporting, auditing, and online analytical processing (OLAP) experience excessive storage input / output (I / O), which wastes valuable computer resources such as time and power due to excessive scanning. BRIEF DESCRIPTION OF THE DRAWINGS

[0004] In the figure:

[0005] Figure 1 is a block diagram depicting an example computer that accelerates filtering of columns in a table by database statements in a workload by interleaving the order of rows in the table and automatically generating, for a number of storage regions containing the rows, an optimal region map that is a mapping from the columns to a range of values ​​in each column;

[0006] Figure 2 is a flow chart depicting an example computer process for accelerating filtering of columns in a table by database statements in a workload by interleaving the rows in the table and automatically generating an optimal extent mapping;

[0007] Figure 3 is a flow chart depicting an example computer process that enables discovery of an optimal interleaving order;

[0008] Figure 4 is a block diagram illustrating a computer system in which embodiments of the present invention may be implemented;

[0009] Figure 5 is a block diagram illustrating a basic software system that may be used to control the operation of a computing system. DETAILED DESCRIPTION

[0010] In the following description, for the purpose of explanation, many specific details will be set forth so that the present invention is fully understood. However, it is apparent that the present invention can be practiced without these specific details. In other cases, well-known structures and devices are shown in block diagram form to avoid unnecessary confusion of the present invention.

[0011] General Overview

[0012] In order to accelerate filtering by database statements, this paper is an interleaved sorting technique that automatically generates optimal mappings from table columns to value ranges in each column for multiple storage areas containing database blocks. In data warehouses and online analytical processing (OLAP) with dimension tables and fact tables, where zone mapping is generally used, the fact table is the largest table and fast data access is critical. There are often a large number of filter predicates on columns in the dimension table, which are usually connected to the fact table on the connection columns. Therefore, the most effective use of zone mapping can be to construct them on dimension table columns. For acceleration, zone mapping can be constructed by first temporarily connecting the fact table to the dimension table and then storing the minimum and maximum values ​​for the relevant dimension table columns in the zone mapping. The novel optimization of zone mapping based on interleaved sorting can be summarized as follows.

[0013] For example, in an embodiment, to perform an interleaved sort on columns A and B of table T, a relational database management system (RDBMS) can create a temporary virtual column based on the bitwise interleaving of the values ​​in columns A and B for each row, and sort table T according to this temporary column. In this article, bitwise interleaving provides ordering of multi-dimensional (e.g., more than two dimensions) data to a single dimension to increase data locality. Interleaved sorting creates an ordering in which, for each unique combination of interleaved columns, all occurrences are located adjacent to each other in a single location (such as a sequence of continuous storage blocks discussed herein). This provides a high degree of locality from which the zone mapping can benefit. In addition, for each individual column involved, interleaved sorting increases data locality without the computational overhead of accurately sorting each column individually.

[0014] As an example, a database table containing customer orders in a retail store is sorted interleaved by the columns "Month" and "ItemId." If most queries are searching for information about certain itemIds in certain months, then this sorting provides high efficiency because all rows with a specific (itemId, month) combination will be located next to each other in a single location.

[0015] The RDBMS can use the zone mapping information to avoid accessing unrelated data blocks. For example, if an item is sold only for a few months (e.g., Christmas trees in December), there may be far fewer than a dozen such locations. As another example, if the "Zip Code" and "State" columns are interleaved, all rows with a specific ZIP code will still be in one location because there is a many-to-one association between ZIP codes and states. In other words, if the data in two columns is related, then interleaving those two columns may improve efficiency.

[0016] For database workloads with multiple database statements, this article is a way to choose which columns to interleave and which columns to configure zone mapping. The following may be important factors that affect whether interleaving columns can increase data locality.

[0017] Column importance: Only columns that account for a large proportion of filter predicates should be considered, because the data access structure is not useful for columns that are queried too infrequently or never.

[0018] Column co-occurrence in queries: If most queries have

[0019] "AND" filter, then efficiency can be improved by performing an interleaved sort on these columns. For example, as discussed above, if most queries on a table search for items with a specific item ID in a specific month, then interleaved clustering of these two columns will be very efficient. However, since sorting a table is an expensive process in terms of computation and storage input / output (I / O), such query workload information can only be used if it is known in advance that the query workload is highly predictable. To this end, our efficiency estimation model does not attempt to identify co-occurring columns within a query, but is instead based solely on the importance of individual columns.

[0020] Query selectivity of columns: Interleaving sorts on columns with high query selectivity provides greater efficiency because only a few regions will be accessed if data locality is sufficient. In contrast, if queries on a particular column typically select the majority of rows, interleaving sorts on that column or region mappings on that column may not help.

[0021] In-list size: If the query for a particular column is a union of a large number of equality predicates (such as in the case of a large Structured Query Language (SQL) IN(...) list), or if the query typically consists of a large number of OR conditions, then region mapping is rarely useful. This is because a large IN() list increases the probability that at least one of the elements in the list is present in a given region.

[0022] Column statistics: Certain columns with low cardinality (i.e., small number of distinct values, NDV) are good candidates for interleaved sorting. This is because the sort splits other interleaved columns into fewer locations. For example, if the "ItemId" column is interleaved with the "Month" column, the same itemId can be in up to twelve different locations, whereas if the itemId is interleaved with the ZIP code, the same itemId can be in thousands of locations (e.g.,

[0023] Each postal code corresponds to a location).

[0024] Relationship with other columns: Columns that are highly correlated with each other are good candidates for interleaved sorting because they divide each other into fewer locations. For example, a “zip code” in a table

[0025] The "State" column can be interleaved with each other to ensure co-location (ie, data locality). As another example, if certain items are only sold in certain zip codes, then the zip code and item ID can be interleaved with each other.

[0026] Due to the complex nature of data dependencies between columns, this approach does not attempt to explicitly model data dependencies. Instead, candidate interleaved sorts are empirically evaluated on data samples, and the corresponding sorting efficiency of the entire original data set is extrapolated from the corresponding sorting efficiency on the sample. Repeated sorting of samples is much faster. However, running the entire workload on the sample may also be slow, especially when there are a large number of candidate sortings to search. To address this problem, this paper is a streamlined model of the query of the workload. By replacing the entire data set with a sample and replacing the workload query with a simplified workload model, alternative interleaved sorts can be compared in seconds (rather than hours), which promotes the efficiency of exploring more sorting in less time. Due to the acceleration of the exploration itself, the computer can automatically estimate the acceleration provided for the workload by many different empirical sortings of the table, which promotes: a) finding a better sorting than the prior art in a fixed exploration time or b) finding the best sorting in less time than the prior art.

[0027] In an embodiment, a computer automatically measures for each column in a plurality of rows: a) the corresponding frequency of statements filtering the column in the workload of database statements, b) the corresponding count of different values ​​used to filter the column individually in each statement, c) the corresponding frequency of each count of different values ​​used to filter the column across all database statements, and d) the corresponding value range of the column for each storage area in a plurality of storage areas. Each storage area contains a corresponding disjoint (i.e., non-overlapping) subset of rows. The sampled subset of rows is randomly selected. Each column contains the corresponding value of each row in the sampled subset of rows. The corresponding efficiency is measured for each of a plurality of different interleaved sorts. Each interleaved sort uses a corresponding different subset of columns. Each interleaved sort is based on the portion of each value of each row in each column of the subset of columns of the interleaved sort for each row in the sampled subset of rows. The efficiency measurement is based on the frequency of statements, the value range of the column of each storage area, and the frequency of the count of different values. The rows are then sorted based on the best interleaved sort with the highest efficiency.

[0028] 1.0 Sample Computer

[0029] Figure 1 1 is a block diagram depicting an example computer 100 in an embodiment. The computer 100 accelerates filtering of columns AD in the table 110 by database statements S1-S2 in the workload 120 by interleaving the rows R1-R4 in the table 110 and automatically generating an optimal region map 160 for the storage region XY containing the rows R1-R4 as a mapping from columns AC to the value ranges in each column. The computer 100 may be one or more of a rack server (such as a blade), a personal computer, a mainframe, a virtual computer, or other computing devices.

[0030] 1.1 Example Query

[0031] Each of the database statements S1-S2 may specify a corresponding create, read, update, or delete (CRUD) command. The database statements S1-S2 may be expressed in a data manipulation language (DML) such as structured query language (SQL). In an embodiment, each of the database statements S1-S2 may specify a query by example (QBE).

[0032] Each of the database statements S1-S2 may or may not specify filtering. For filtering, the database statement may reference one or more columns (including zero or more of columns AD) in one or more tables, which may or may not include table 110. In this example, database statement S1 is the following SQL query.

[0033] SELECT D FROM TABLE 110WHERE A<>B AND A IN('Chicago','Denver','Boston')

[0034] The WHERE clause in database statement S1 specifies filtering of table 110 based only on columns AB. Database statement S1 references column D, but it is not used for filtering. Database statement S1 does not reference column C.

[0035] In this example, the database statement S2 is the following SQL query containing a subquery.

[0036] SELECT MAX(C)FROM TABLE_110WHERE A<>'Richmond'AND B='NJ'OR C<(SELECTCOUNT(*)FROM TABLE WHERE A IS NOT NULL AND C IS NOT NULL)

[0037] Database statement S2 contains two WHERE clauses. For the purposes of this article, the two WHERE clauses can be concatenated together and analyzed together as a single combined WHERE clause. The combined WHERE clause in database statement S2 specifies filtering of table 110 based only on columns AC. Database statement S2 does not reference column D.

[0038] 1.2 Workload Statistics Example

[0039] In the illustrated embodiment, column D is excluded from further analysis and is not shown in workload 120 because none of the database statements S1-S2 filter column D. In workload 120, each of columns AB is filtered by two statements, and column C is filtered by one statement. These counts are unit normalized to the corresponding weights for columns AC. For example, as shown for weight 130, column B has a weight of 1.0, which is the maximum possible weight and indicates that all database statements S1-S2 filter column B.

[0040] The filter cardinality of a column is the count of literal values ​​specified for that column in a particular database statement. For example, column A has a filter cardinality of three in database statement S1 because database statement S1 includes "Chicago," "Denver," and "Boston" as literal values ​​for filtering column A. Database statement S1 does not include (i.e., zero) literal values ​​for filtering column BC. For each of statements S1-S2, respectively, the filter cardinality for columns AC is shown for count 140.

[0041] The <> inequality operator in statement S2 is a special case based on the contents of column A. For statement S2, the filter cardinality of column A is one less than the cardinality of the contents of column A. For example, if column A contains thirty distinct values, then for statement S2, the filter cardinality of column A is 30-1=29.

[0042] Heterogeneity 150 is based only on counts 140. For heterogeneity 150, a corresponding probability density for each of columns AC is generated by binning the filtered cardinality for counts 140 into a histogram for each column. In this example, each histogram has four intervals, numbered 0-3. For example, for each count 140, the filtered cardinality of column B is zero and one for the corresponding database statements S1-S2, and those cardinality corresponds to intervals 0-1, with each interval showing a unit normalized probability of 0.5. The cardinality is calculated as a count, but the cardinality can be used as an identifier for the interval to increment by one. After filling the interval by incrementing, the corresponding tally in each interval is unit normalized. Therefore, for a given column (i.e., for the histogram shown for that column), the unit normalized probability sums to one.

[0043] 1.3 Example Zone Mapping

[0044] All measurements 130, 140, and 150 are made without actually accessing table 110. That is, workload 120 depends on the structure of table 110, not the contents (e.g., row counts) of table 110. In contrast, region map 160 is based on which values ​​in columns AD appear in each of storage regions XY, each of which stores a corresponding horizontal slice of table 110 in a row-major format. In this example, region X contains rows R1-R3, and region Y contains row R4.

[0045] In an embodiment, each zone contains a corresponding disjoint (i.e., non-overlapping with another zone) sequence of contiguous storage blocks (e.g., disk blocks or database blocks or virtual memory pages) that contain a horizontal slice of the zone's table 110. In an embodiment, a zone is contained in at most one disk track, which means that reading the entire zone requires at most one track seek, a first rotational latency to find the start of the zone, and a second rotational latency to reach the end of the zone.

[0046] Depending on the embodiment, each region may or may not contain the same count of objects (such as memory blocks, table rows, or column values). Depending on the embodiment, each memory block may or may not contain the same count of objects (such as table rows or column values).

[0047] In an embodiment not shown, instead of a zone map 160 that tracks multiple columns, each column has its own zone map that tracks only that column. In an embodiment, table 110 is partitioned horizontally, and each partition has its own zone map. For example, multiple partitions can be hosted by computer 100 or by separate respective computers. For example, computer 100 can generate an optimized zone map for data hosted by another computer.

[0048] The zone map 160 tracks the range of values ​​(i.e., minimum and maximum values) in each of the zones XY for each of the columns AD. The mechanism used to determine the minimum and maximum values ​​depends on the data type of the column. For example, the minimum and maximum values ​​for column C are determined by numerical comparison of numbers.

[0049] Likewise, the minimum and maximum values ​​for column A are determined by lexical comparison of text strings. For example, as shown in zone map 160, the values ​​are alphabetically arranged from "Boston" to "Denver" in zone X and from "Paris" to "Paris" in zone Y. In this example, an empty string is shown as "". Depending on the embodiment, an empty value may or may not be treated as an empty string or zero.

[0050] In an embodiment, region map 160 may be updated when rows are modified, added, removed, or sorted in table 110. For example, deleting any of rows R2-R3 in region X (but not R1) causes statistics for column A in region map 160 to be updated.

[0051] The region map 160 accelerates filtering of columns of the table 110 when executing a database statement, even if the statement is not in the workload 120. For example, when executing database statement S1, the region map 160 can be used to detect that filtering of column A excludes all rows in region Y. In that case, filtering by statement S1 is accelerated by not accessing (e.g., not scanning) region Y.

[0052] 1.4 Example efficiency evaluation

[0053] When table 110 is sorted, extent map 160 should be repopulated because sorting can cause rows to move from the first extent to the second extent, which can affect the content statistics of either or both extents. When extent map 160 is updated, the speed of filtering can be increased or decreased for some database statements because how many extents and which extents need to be accessed during filtering can change. Sorting does not affect the results of a statement, but it can affect the speed of the statement.

[0054] How many columns and which columns are used for sorting affects the content statistics of the zones tracked by zone map 160. Therefore, different sortings can cause the same statement to execute at different corresponding speeds. The choice of sorting columns can increase or decrease the aggregate speed of a dynamic workload or a static workload (such as workload 120). The speed of a dynamic (e.g., unpredictable or fluctuating) workload can be empirically measured by executing the workload on a given sorting of table 110. Instead, the speed of a static workload 120 on a given sorting of table 110 is estimated (i.e., predicted) based on an estimation model that does not actually execute statements S1-S2. The estimation model uses the statistics in workload 120 and the statistics in zone map 160 together in a synergistic manner to accurately reflect the data access of statements S1-S2. Because the estimation model does not actually execute statements S1-S2 and minimally accesses the contents of table 110, it is accelerated. As a result of this speedup, computer 100 can estimate the speed provided for workload 120 by performing many different empirical orderings of table 110 (or subsets of its rows, as discussed later in this document), which facilitates: a) finding a better ordering in a fixed time period than the prior art, or b) finding the best ordering in less time than the prior art.

[0055] In an embodiment, the estimation model applies the following estimation formula to predict the speed provided by a particular ordering. In particular, the estimation formula estimates the latency (e.g., total duration) of the workload 120 instead. Because latency is mathematically or informally the inverse of speed, converting latency to speed can be more or less straightforward. More precisely, the following estimation formula predicts latency as a count of regions to be accessed.

[0056]

[0057] The following terms in the estimation formula have the following meanings.

[0058] Q is the workload 120.

[0059] c is a filtered column in workload 120 (ie, not the unfiltered column D in workload 120).

[0060] ·w c The weight for the filtered columns is 130 (eg, 1.0 for column B).

[0061] Z is all storage regions XY in the region map 160 .

[0062] ·z j is the jth storage area.

[0063] ·NDV cis the number of distinct values ​​(NDV) actually stored in the filtered column (or a subset of its rows, as explained later herein) in table 110.

[0064] · is the value range of the filter column in the jth region as recorded in the region map 160 .

[0066] ·V c yes The cardinality of .

[0067] · is the average filter cardinality of the filtered column.

[0068] Cardinality V c This can be measured by how many distinct values ​​in the filter column are included in the value range of the jth zone in the entire table 110 (i.e., all zones XY). For example, if zone Y also contains a row R5 (not shown) containing the value "FL" in column B, then the distinct value "FL" is for zone X (even if zone X does not contain the value "FL") and NDV c V c Both increase by one, where c is column B.

[0069] Average filter base is the weighted average of the histogram bin numbers discussed earlier in this document for heterogeneity 150, where the weight of the bin is the unit normal fraction shown in the bin of the histogram of the filtered column for heterogeneity 150. For example, when c is column A, the weighted average is 0.5x2+0.5x3=2.5, which is the average filtered cardinality of column A.

[0070] 1.5 Interleaved sorting

[0071] Different sortings for table 110 can be compared based on corresponding calls to the estimation formula. For example, the best sorting can be the sorting that provides the maximum speed (i.e., the minimum latency according to the estimation formula). In this article, each sorting is an interleaved sorting, which is a special and atypical way to sort the rows of a table. Conventional multi-column sorting prioritizes (i.e., arranges in order) the sorting of each sorting column. For example, a SQL sorting clause such as "ORDER BY C, A" uses column C as the primary sorting column and uses column A as the secondary sorting column for sorting. In other words, sorting columns A and C are not treated equally in terms of importance and effect. For example, instead, "ORDER BY A, C" specifies different sortings.

[0072] In this article, interleaved sorting treats all sort columns as equal. Unlike conventional sorting, the sort columns of interleaved sorting are not specified as sequences, but instead are specified as unordered sets. In this article, interleaved sorting is based on parts of the values ​​in the sort columns, which is unlike conventional multi-column sorting that is based only on the entire values ​​in the sort columns.

[0073] Depends on the embodiment and / or data type of the sorting column, a part of the value of the sorting column can be one or more bits, one or more bytes / octets or one or more characters (e.g., for a string column). The smaller the part, the more equal the sorting effect of the column, wherein a part provides maximum equality. In this article, the interweaving of the parts of the values ​​of a plurality of sorting columns is the mode of generating the sorting key of the row of the table being sorted. Corresponding interleaving sorting keys can be generated for each row in row R1-R4.

[0074] For example, the interleaved sorting of table 110 can be based on column BC as the sorting column, and the interleaved sorting key for row R3 can be generated by decomposing the value "MA" shown into a sequence of three-bit parts and by decomposing the value 10 shown into a sequence of three-bit parts. Those two sequences of parts can be combined into an interleaved cascade of three-bit parts. The cascade repeatedly alternates between the next part from column B and the next part from column C.

[0075] In an embodiment, each character in "MA" is a byte having eight bits. In other words, "MA" has 2x8=sixteen bits. In an embodiment, the value 10 is a long integer formatted as a machine word having 64 bits. In that case, "MA" has six three-bit parts, while the value 10 has 22 three-bit parts. In that case, the first six (i.e., most significant) of the three-bit parts of each column are interleaved, while the remaining sixteen parts of the value 10 are concatenated to the sort key without interleaving.

[0076] In an embodiment, null values ​​and empty strings have no bits and parts. In an embodiment, null values ​​have a predefined sequence of reserved bits that can be divided into multiple parts. In an embodiment, an empty string has an integer count (i.e., zero) bytes, which is a length indicator that can be divided into multiple parts.

[0077] 1.6 Example sorting optimization

[0078] In an embodiment, each empirical interleaving order has its own order lifecycle, which has two states: unapplied and applied. Applied means: a) the table 110 (or a subset of its rows, as explained later in this article) is sorted using the interleaving order, and b) the estimation model (e.g., the estimation formula) predicts the latency (i.e., the count of the visited areas). Unapplied means that the sorting column of the interleaving order has been selected, but the sorting has not yet occurred. In this article, an unapplied order can be synonymous with its collection of sorting columns.

[0079] The exploration to find the best interleaving order can be based on iterative use of a queue 170 that buffers unapplied interleaving orders that are generated by incrementally adding another order column to the order column of an applied interleaving order. Depending on the embodiment, access to the contents of the queue 170 is: first-in, first-out (FIFO), random access, or both. The queue 170 can be implemented as an array or a linked list.

[0080] The queue 170 is illustrative. Pseudocode for slightly different exemplary embodiments is presented later herein. The discussion of the queue 170 and its operation herein is not limited to the exemplary embodiments presented later herein.

[0081] In an embodiment, the operation of queue 170 may need a sequence of iterations 0-5 in this example. In each iteration, the element at the head of queue 170 will be out of the queue and processed. Each element in queue 170 is a different set of sorting sequences for different interleaving sorts. Depending on the embodiment, the element in queue 170 may have as few as zero (according to the illustrated embodiment) or one (in the embodiment provided later in this article) sorting sequence. In the illustrated embodiment, queue 170 initially only comprises the element of the empty set with sorting sequence, as shown in iteration 0.

[0082] Each iteration begins by dequeuing the current element from the head of queue 170, which in iteration 0 is an empty set representing an unsorted sort column of table 110 (or a subset of its rows, as explained later herein). If the current element is not an empty set, then the current iteration interleaves the sort column of table 110 using only the current element's sort column. Statistics in region map 160 are recalculated to reflect the interleave sorting that occurred in the current iteration.

[0083] The estimation model predicts the counts of the visited regions for the current interleaving ordering based on components 110, 120, and 160 (such as according to the estimation formulas given earlier herein). If the current count of the visited region is less than the minimum count seen so far in all previous iterations, then the current count replaces the minimum count, and the set of sorted columns for the current interleaving ordering is retained as a representation of the best interleaving ordering so far.

[0084] The current iteration ends by incrementally generating zero or more new unapplied interleaving sorts and appending them to the tail of the queue. Incrementally generating new unapplied interleaving sorts from the current interleaving sort requires adding another corresponding sort column to each new sort. The additional columns are selected from the columns included in the workload 120 (e.g., excluding column D).

[0085] The generation of the following interleaved orderings is excluded. The orderings are different and should not redundantly generate previously generated (applied or not) interleaved orderings. Each ordering has a different set of ordering columns and the generated orderings should not have the same ordering columns appear multiple times.

[0086] In iteration 0, where the current element is an empty set, three new unapplied interleaved sortings are generated, each with a single different sorting column. At the start of each iteration, the contents of the queue 170 for that iteration are shown as one element per line of text (i.e., a collection of sorting columns). Iteration 1 shows three lines of text representing the three unapplied interleaved sortings generated and queued in iteration 0 and are the contents of the queue 170 at the start of iteration 1.

[0087] Elements are shown in bold in the iteration immediately after the element is enqueued. In subsequent iterations, elements are not shown in bold. For example, an interleaved sort with only column AB as the sort column is generated and enqueued before iteration 2, and is shown in bold in iteration 2 and non-bold in iterations 3-4.

[0088] The head of the queue 170 is shown as the top row of text in the iteration. For example, in iteration 3, an interleaved ordering with only column C is dequeued from the head of the queue 170.

[0089] Various embodiments may have one or more of the following example stopping criteria that cause an iteration to stop when the current iteration ends or when evaluated at other times as given later in this document: queue 170 is empty; the current count of region accesses drops below a first threshold; all possible interleaving orderings with fewer sorted columns than a second threshold have been applied; and convergence (i.e., the improvement in the fewest region accesses to date drops below a third threshold).

[0090] 2.0 Example of interleaving sorting optimization process

[0091] Figure 2 1 is a flowchart depicting an example process that may be performed by computer 100 in an embodiment to accelerate filtering of columns AD in table 110 by database statements S1-S2 in workload 120 by interleaving ordering rows and automatically generating an optimal extent map 160 for storage area XY containing the rows as a mapping from columns AC to ranges of values ​​in each column. Figure 1 discuss Figure 2 .

[0092] Steps 201-203 find the best interleaving order for table 110. Step 201 automatically measures workload statistics for each column. For example, step 201 populates statistics of workload 120 as discussed earlier herein, including weights 130, counts 140, and heterogeneity 150 for columns AC.

[0093] Table 110 may have dozens of columns and billions of rows, which may be difficult to re-order. To speed up exploration, step 202 randomly selects a sampled subset of the rows of table 110. Instead of the entire table 110, the random sample is used to find the best interleaving order. In an embodiment that further speeds up exploration, the random sample contains only the filtered columns of workload 120 (i.e., excludes column D).

[0094] In an embodiment, at most 0.2% of the rows of table 110 are sampled. In an embodiment, at least twenty rows are sampled from each zone. In an embodiment discussed later in this document, the random samples are stored in a separate database table. In an embodiment, the separate table contains (one or more) key columns that can be used to cross-reference the original row from the sample row. For example, the (one or more) key columns can store a (e.g., composite) primary key of table 110 or a row identifier (ROWID) of the corresponding row of table 110. In an embodiment, the separate table contains a column indicating which zone of table 110 the sampled row is from. In an embodiment, the zone is instead determined based on the logical block address (LBA) specified in the ROWID in question.

[0095] Step 203 measures the corresponding efficiency of each empirical interleaving ordering. In an embodiment, step 203 performs iterations 0-5 with queue 170, as discussed earlier herein. When applying an interleaving ordering and measuring the efficiency provided by the ordering, step 203 uses a sampled subset of rows instead of the entire table 110 for speed.

[0096] The result of step 203 is to find the best interleaving order with the highest efficiency, as discussed earlier herein. Before performing steps 204-205, the sampled subset of rows should be sorted based on the best interleaving order. Depending on the embodiment, steps 204-205 may or may not occur. The purpose of steps 204-205 is to reduce the size of the region map 160 by excluding (one or more) columns for which the region map 160 empirically does not provide workload acceleration.

[0097] Step 204 remeasures the efficiency provided by the optimal interleaving ordering by temporarily excluding any particular column from the region map 160. If the statistics of ignoring a particular column in the region map 160 causes the efficiency to drop below a threshold, then the particular column is important and should be retained in the region map 160. In an embodiment, step 204 applies the following impact formula to measure the efficiency loss caused by temporarily excluding a particular column.

[0098]

[0099] The following items have the following meanings in the impact formula.

[0100] ·E[A Q ] is the efficiency when the entire zone map 160 is used, for example as measured by the estimation formula given earlier in this document.

[0101] ·c is the temporarily excluded column.

[0102] · is the efficiency (eg, reduced) in ignoring statistics for columns that are excluded in the region map 160 .

[0103] Step 205 detects whether the efficiency loss measured in step 204 exceeds a threshold. In an embodiment, the threshold is 0.05 (ie, 5%). Step 205 causes step 206 to exclude the particular column(s) from the final zone map generated by step 206, as discussed below.

[0104] Step 206 sorts all rows of table 110 based on the best interleaved sort. In other words, the interleaved sort key generation as discussed above is applied to each row of table 110.

[0105] The result of step 206 is that table 110 is optimally sorted for a workload that is more or less similar to workload 120. Because the interleaved sorting performed by step 206 uses only portions of columns, the rows in any region of table 110 and the rows in table 110 as a whole are not actually sorted by any entire column. In other words, the result of the optimal interleaved sorting may still appear unsorted for many purposes, including example purposes such as executing database statements S1 and / or S2. For example, the sorting specified in the query should always occur at query execution time, even if table 110 has been optimally interleaved sorted by step 206.

[0106] After the interleaved sorting by step 206, step 206 should also completely regenerate the region map 160, which requires more than just recalculating existing statistics in the region map 160. As discussed later herein, sorting the table 110 by step 206 may sometimes result in the introduction of new regions and the removal of some or all old regions, which may require a complete reconstruction of the region map 160. For example, depending on the embodiment, the sorting of the table 110 occurs in-place or out-of-place (e.g., by swapping rows to reorder them).

[0107] 3.0 Example activity for discovering the best interleaving order

[0108] Figure 3 is a flow chart depicting example activities that an embodiment of computer 100 may perform to enable discovery of an optimal interleaving ordering. Figure 2-Figure 3 The steps of the process are complementary and can be combined or interleaved. Figure 1 discuss Figure 3 .

[0109] Step 301 performs an operation that enables the optimal interleaving order to be found and the efficiency of the optimal interleaving order to be measured. In one example, the operation is the execution of a command specifically used only for interleaving order optimization and zone (re) mapping. In one example, the operation is the execution of a command that explicitly requests interleaving order optimization and zone (re) mapping as optional additional activities. In one example, the operation is the execution of a command that does not request interleaving order optimization and zone (re) mapping, but the computer 100 automatically decides to supplement the operation by also performing interleaving order discovery and zone (re) mapping. In one example and in the absence of a command from a database client, the operation is an autonomous decision to perform interleaving order optimization and zone (re) mapping.

[0110] For example, the operation may shrink table 110, which requires compression, such as moving some rows to fill gaps in database blocks that may have been previously stored due to the deletion of rows. Figure 2 As explained in step 206 of , the operation of moving many rows causes rows to change regions and may cause regions to be added or removed. Based on the operation destroying the arrangement of stored rows or based on the operation causing many new rows to be generated, step 301 leads to steps 302-306 and also causes: finding a new optimal interleaving order; reordering table 110 based on the new optimal order, and regenerating region map 160.

[0111] In one example, the operation is the execution of a data manipulation language (DML) statement (such as a SELECT INTO statement). In one example, the operation is the execution of a data definition language (DDL) statement (such as a CREATE TABLE AS SELECT statement).

[0112] Step 302 detects that the data type of a particular column is a timestamp or that a particular column lacks (eg, prohibits) duplicate values. Automatic checking of the database schema may reveal that a particular column is configured as UNIQUE or as an automatically generated sequence (eg, a primary key value).

[0113] Those are examples of columns that are unlikely to improve efficiency when used as one of the sort columns of an interleaved sort. In that case, step 303 may exclude the particular column from workload 120, even if database statements S1-S2 filter the particular column.

[0114] Based on the priority queue, step 304 generates a plurality of different empirical interleaving orderings. For example, queue 170 can be a priority queue that operates iteratively more or less as discussed above herein. In one example, a new unapplied interleaving ordering can be inserted into a position in the queue such that the new ordering is inserted before all queued unapplied orderings that are incrementally generated from any applied ordering whose efficiency is lower than the efficiency of the applied ordering from which the new ordering is generated. For example, a new ordering incrementally generated from the current best ordering is inserted into the head of the queue.

[0115] Step 305 appends the first specific set of sorted columns to a priority queue that is not necessarily empty. Step 306 demonstrates an activity that would not occur if queue 170 were not a priority queue. At the end of the priority queue, step 306 appends a second specific set of sorted columns that has fewer sorted columns than the first specific set of sorted columns. In other words, unlike Figure 1 , the queue 170 shown in , may contain a sequence of unapplied sorts whose size is not monotonically increasing.

[0116] 4.0 Exemplary Embodiments

[0117] The following exemplary embodiment is based on the embodiments given earlier in this document. The design choices demonstrated by this exemplary embodiment are not limitations of the embodiments given earlier in this document. Figure 1 Discuss exemplary embodiments.

[0118] 4.1 Example Sampling

[0119] In an exemplary embodiment, sample creation occurs as follows. After candidate columns are identified (e.g., columns AC in workload 120), a materialized sample of only those columns is created. This sample is then analyzed to compute the best ordering and also identify zone mapping columns. Given a set of candidate columns C = {c1, ..., ci, ..., cnC}, a fact table F, a set of dimension tables D, and a set of fact-dimension join conditions J, the following example SQL statements can create a sample.

[0120] CREATE TABLE clustering_sample AS SELECT rowid row_id,C FROM F SAMPLE($sampling_percentage),D WHERE J;

[0121] The join condition J is reconfigured to use a left outer join instead of the usual default equijoin for fact-dimension joins. The reason for the join reconfiguration is that if the fact table row is joined with other tables, then do not exclude it from the sample simply because it is not joined with one dimension table. For better clustering accuracy, a row sample is created instead of a storage block sample. The ROWID is saved as part of the sample because clustering by ROWID facilitates the default behavior of analyzing the original fact table in the absence of any other clustering. In this article, clustering is synonymous with interleaved sorting.

[0122] 4.2 Exemplary Interleaving Sorting Optimization Algorithm

[0123] The following Algorithm 1 is a pseudo code for finding the best clustering solution. The algorithm is greedy. At each iteration, it selects the cluster with the least estimated number of zone visits and further expands the current solution by adding candidate columns that are not part of the current solution to the current solution. In this paper, in a database table (such as a table that stores rows sampled from another table), a virtual column has calculated content that can be materialized or not. Algorithm 1 calls the following function.

[0124] Interleave_and_Sort(S, new_cand_cols) first creates temporary virtual columns by interleaving the columns in the list new_cand_cols, and then interleaves and sorts the samples on these columns. This function is used by relational database management systems (RDBMS)

[0125] supply.

[0126] Expected_Num_Accesses(Clustered_Sample) takes a clustered sample as input and returns the expected number of regions accessed for the workload using the estimation formula given earlier in this article.

[0127] In addition to its input parameters, the function expected_Num_Accesses(Clustered_Sample) also has the following information related to the workload it is available for.

[0128] Weight w c Append to each column as discussed earlier in this article for the estimation formula.

[0129] · Mean is the average number of distinct values (NDV) on a column, as discussed earlier in this document for the estimation formula.

[0130] By running Algorithm 1, the best solution for the column on which to cluster is found. Algorithm 1 accepts the following as input.

[0131]

[0132] After running Algorithm 1, given this clustering, the best solution for the column on which to cluster and the expected number of region mappings for randomly selected query accesses are available.

[0133] 5.0 Database Overview

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

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

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

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

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

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

[0140] A multi-node database management system consists of interconnected nodes that share access to the same database. Typically, the nodes are interconnected via a network and share access to shared storage to varying degrees, such as shared access to a set of disk drives and data blocks stored thereon. The nodes in a multi-node database system may be in the form of a group of computers (e.g., workstations, personal computers) interconnected via a network. Alternatively, the nodes may be nodes of a grid consisting of nodes in the form of server blades interconnected to other server blades on a rack.

[0141] Each node in a multi-node database system hosts a database server. A server, such as a database server, is a combination of integrated software components and an allocation of computing resources, such as memory, nodes, and processes on the nodes for executing the integrated software components on processors, a combination of software and computing resources dedicated to performing specific functions on behalf of one or more clients.

[0142] Resources from multiple nodes in a multi-node database system can be allocated to run the software of a particular database server. Each combination of software and allocation of resources among nodes is a server referred to herein as a "server instance" or "instance." A database server can include multiple database instances, some or all of which run on separate computers (including separate server blades).

[0143] 5.1 Query Processing

[0144] A query is an expression, command, or set of commands that, when executed, causes a server to perform one or more operations on a data set. A query may specify source data objects (or objects), such as tables (or objects), columns (or objects), views (or objects), or snapshots (or objects), from which result sets (or objects) are to be determined. For example, source data objects (or objects) may appear in the FROM clause of a Structured Query Language ("SQL") query. SQL is a well-known example language for querying database objects. As used herein, the term "query" is used to refer to any form of representation of a query, including queries in the form of database statements and any data structures used for internal query representation. The term "table" refers to any source object, such as a database table, view, or inline query block (such as an inline view or subquery), that is referenced or defined by a query and represents a set of rows.

[0145] Queries can perform operations on data from the source data object(s) row by row as the object(s) are loaded, or on the entire source data object(s) after the object(s) have been loaded. Result sets generated by some operations can make available to other operations(s), and in this way, result sets can be filtered out or narrowed based on certain criteria, and / or joined or combined with other result sets(s) and / or other source data objects(s).

[0146] A subquery is a portion or component of a query that is distinct from the other portion(s) or component(s) of the query and can be evaluated separately from the other portion(s) or component(s) of the query (i.e., as a separate query). The other portion(s) or component(s) of the query can form an outer query, which may or may not include other subqueries. A subquery nested within an outer query can be evaluated separately one or more times, while computing results for the outer query.

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

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

[0149] Hardware Overview

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

[0151] For example, Figure 4 4 is a block diagram illustrating a computer system 400 upon which embodiments of the present invention may be implemented. Computer system 400 includes a bus 402 or other communication mechanism for communicating information, and a hardware processor 404 coupled with bus 402 for processing information. Hardware processor 404 may be, for example, a general purpose microprocessor.

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

[0153] Computer system 400 also includes a read only memory (ROM) 408 or other static storage device coupled to bus 402 for storing static information and instructions for processor 404. A storage device 410, such as a magnetic disk, optical disk, or solid state drive, is provided and coupled to bus 402 for storing information and instructions.

[0154] The computer system 400 may be coupled to a display 412, such as a cathode ray tube (CRT), via the bus 402 for displaying information to a computer user. An input device 414, including alphanumeric and other keys, is coupled to the bus 402 for communicating information and command selections to the processor 404. Another type of user input device is a cursor control 416, such as a mouse, trackball, or cursor direction keys, for communicating direction information and command selections to the processor 404 and for controlling cursor movement on the display 412. Such input devices typically have two degrees of freedom in two axes, a first axis (e.g., an x-axis) and a second axis (e.g., a y-axis), which allows the device to specify a position in a plane.

[0155] Computer system 400 may implement the techniques described herein using custom hardwired logic, one or more ASICs or FPGAs, firmware, and / or program logic that is combined with a computer system to make or program computer system 400 into a special purpose machine. According to one embodiment, computer system 400 performs the techniques described herein in response to processor 404 executing one or more sequences of one or more instructions contained in main memory 406. These instructions may be read into main memory 406 from another storage medium, such as storage device 410. Execution of the sequences of instructions contained in main memory 406 causes processor 404 to perform the process steps described herein. In alternative embodiments, hardwired circuitry may be used in place of or in combination with software instructions.

[0156] The term "storage medium" as used herein refers to any non-transient 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 410. Volatile media include dynamic memory, such as main memory 406. 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 cassette tapes.

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

[0158] Various forms of media may be involved in transmitting one or more sequences of one or more instructions to processor 404 for execution. For example, the 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 send the instructions over a telephone line using a modem. A modem local to computer system 400 may receive the data on the telephone line and use an infrared transmitter to convert the data into an infrared signal. An infrared detector may receive the data carried in the infrared signal and appropriate circuitry may place the data on bus 402. Bus 402 transfers the data to main memory 406, from which processor 404 retrieves and executes the instructions. The instructions received by main memory 406 may optionally be stored on storage device 410 before or after execution by processor 404.

[0159] Computer system 400 also includes a communication interface 418 coupled to bus 402. Communication interface 418 provides bidirectional data communication coupled to network link 420, wherein network link 420 is connected to local network 422. For example, communication interface 418 can be an integrated services digital network (ISDN) card, a cable modem, a satellite modem, or a modem that provides a data communication connection with a corresponding type of telephone line. As another example, communication interface 418 can be a local area network (LAN) card to provide a data communication connection with a compatible LAN. A wireless link can also be implemented. In any such implementation, communication interface 418 sends and receives electrical signals, electromagnetic signals, or optical signals that carry digital data streams representing various types of information.

[0160] The network link 420 typically provides data communication to other data devices through one or more networks. For example, the network link 420 can provide a connection to a host computer 424 or to data equipment operated by an Internet service provider (ISP) 426 through a local network 422. The ISP 426 in turn provides data communication services through a global packet data communication network, now commonly referred to as the "Internet" 428. Both the local network 422 and the Internet 428 use electrical, electromagnetic, or optical signals that carry digital data streams. The signals through the various networks and the signals on the network link 420 and through the communication interface 418 (which carry the digital data to and from the computer system 400) are example forms of transmission media.

[0161] Computer system 400 can send messages and receive data, including program code, through the network(s), network link 420, and communication interface 418. In the Internet example, server 430 can transmit the requested code for an application program through Internet 428, ISP 426, local network 422, and communication interface 418.

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

[0163] Software Overview

[0164] Figure 5 is a block diagram of a basic software system 500 that may be used to control the operation of computing system 400. Software system 500 and its components (including their connections, relationships, and functions) are merely exemplary and are not meant to limit implementation of the example embodiment(s). Other software systems suitable for implementing the example embodiment(s) may have different components, including components with different connections, relationships, and functions.

[0165] Software system 500 is provided for directing the operation of computing system 400. Software system 500, which may be stored on system memory (RAM) 406 and fixed storage (eg, hard disk or flash memory) 410, includes a kernel or operating system (OS) 510.

[0166] OS 510 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 502A, 502B, 502C ... 502N, can be "loaded" (e.g., transferred from fixed storage 410 into memory 406) for execution by system 500. Applications or other software intended for use on computer system 400 can also be stored as a downloadable set of computer-executable instructions, for example, for downloading and installation from an Internet location (e.g., a Web server, app store, or other online service).

[0167] The software system 500 includes a graphical user interface (GUI) 515 for receiving user commands and data in a graphical (e.g., "click" or "touch gesture") manner. In turn, these inputs can be operated by the system 500 according to instructions from the operating system 510 and / or (one or more) applications 502. The GUI 515 is also used to display the results of operations from the OS 510 and (one or more) applications 502, so that the user can provide additional input or terminate the session (e.g., log off).

[0168] The OS 510 may execute directly on the bare hardware 520 (e.g., the processor(s) 404) of the computer system 400. Alternatively, a hypervisor or virtual machine monitor (VMM) 530 may be inserted between the bare hardware 520 and the OS 510. In this configuration, the VMM 530 acts as a software "buffer" or virtualization layer between the OS 510 and the bare hardware 520 of the computer system 400.

[0169] The VMM 530 instantiates and runs one or more virtual machine instances ("guest machines"). Each guest machine includes a "guest" operating system (such as OS 510), and one or more applications (such as (one or more) applications 502) designed to execute on the guest operating system. The VMM 530 presents a virtual operating platform to the guest operating system and manages the execution of the guest operating system.

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

[0171] In other cases, the guest operating system may be specifically designed or configured to execute on the VMM 530 for increased efficiency. In these instances, the guest operating system "is aware" that it is executing on the virtual machine monitor. In other words, the VMM 530 can provide para-virtualization to the guest operating system in certain cases.

[0172] A computer system process includes the allocation of hardware processor time, and the allocation of memory (physical and / or virtual), the allocation of memory for storing instructions executed by the hardware processor, for storing data generated by the execution of instructions by the hardware processor, and / or for storing the hardware processor state (e.g., the contents of registers) between allocations of hardware processor time when the computer system process is not running. Computer system processes run under the control of an operating system and can also run under the control of other programs executable on the computer system.

[0173] Cloud computing

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

[0175] A cloud computing environment (sometimes referred to as a cloud environment or just a cloud) can be implemented in a variety of different ways to best suit different requirements. For example, in a public cloud environment, the underlying computing infrastructure is owned by an organization that makes its cloud services available to other organizations or the public. In contrast, a private cloud environment is generally intended for use 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) bound together by data and application portability.

[0176] In general, the cloud computing model enables some of those responsibilities that may have previously been provided by an organization's own information technology department to be delivered instead as a service layer within a cloud environment for use by consumers (either internally or externally to the organization, depending on the public / private nature of the cloud). Depending on the specific implementation, the precise definition of the components or features provided by or within each cloud service layer may vary, but common examples include: Software as a Service (SaaS), in which consumers use software applications running on a cloud infrastructure, while the SaaS provider manages or controls the underlying cloud infrastructure and applications. Platform as a Service (PaaS), in which consumers can use software programming languages ​​and development tools supported by the supplier of the PaaS to develop, deploy, and otherwise control their own applications, while the PaaS provider manages or controls other aspects of the cloud environment (i.e., everything under the runtime execution environment). Infrastructure as a Service (IaaS), in which consumers can deploy and run arbitrary software applications, and / or provide processes, storage, networks, and other basic computing resources, while the IaaS provider manages or controls the underlying physical cloud infrastructure (i.e., everything below the operating system layer). Database as a Service (DBaaS), where the consumer uses a database management system or database server running on a cloud infrastructure, while the DbaaS provider manages or controls the underlying cloud infrastructure and applications.

[0177] The above basic computer hardware and software and cloud computing environment are presented to illustrate the basic underlying computer components that can be used to implement the example embodiment(s). However, the example embodiment(s) are not necessarily limited to any particular computing environment or computing device configuration. Alternatively, according to the present disclosure, the example embodiment(s) may be implemented in any type of system architecture or processing environment that a person skilled in the art would understand as being capable of supporting the features and functions of the example embodiment(s) presented herein.

[0178] In the foregoing description, embodiments of the present invention have been described with reference to numerous specific details, which may vary from implementation to implementation. Accordingly, the description and drawings should be viewed in an illustrative rather than a restrictive sense. The sole and exclusive indicator of the scope of the invention and what the applicant intends to be the scope of the invention is the literal and equivalent range of the resulting claims in the specific form of the set of claims issued from this application, including any subsequent corrections.

Claims

1. A computer-implemented method, include: Automatically measure each of multiple columns in multiple rows: a respective frequency of statements filtering on the column in a plurality of database statements, a respective count of distinct values ​​used to filter on the column in each of the plurality of database statements, a respective frequency of each of the counts of distinct values ​​for filtering the column in the plurality of database statements, and a respective range of values ​​of the column for each of a plurality of buckets, wherein each of the plurality of buckets contains a respective disjoint subset of the plurality of rows; randomly selecting a sampled subset of the plurality of rows; The corresponding efficiency is measured for each of a plurality of different interleaving orderings, where: Each column of the plurality of columns contains a corresponding value for each row in the sampled subset of the plurality of rows, interleaving the ordering to have respective different subsets of the plurality of columns, interleaving the portion of each of the values ​​for each row in the sampled subset of the plurality of rows in each column of the subset of the plurality of columns based on the interleaved ordering, and Measuring efficiency is based on: a) the frequency of statements, b) the range of values ​​of the plurality of columns for each of a plurality of memory areas, and c) the frequency of the counts of distinct values; and The plurality of rows are ordered based on a best interleaving order having the highest efficiency among a plurality of different interleaving orderings.

2. The method according to claim 1, further comprising: include: measuring a second efficiency based on the optimal interleaving order and the value ranges of the plurality of columns, excluding specific columns from the plurality of columns; When the second efficiency exceeds a threshold, a mapping of each column excluding the specific column from the plurality of columns to the value range of the column is generated.

3. The method according to claim 1, further comprising: include: For each of the plurality of horizontal partitions of the plurality of rows, a corresponding mapping from a particular column of the plurality of columns to the range of values ​​of the particular column is generated for each of the plurality of storage areas.

4. The method of claim 1, wherein the best interleaving order is based on null values ​​in the values ​​that are not composed of any of the portions of each of the values.

5. The method of claim 1, wherein the optimal interleaving order is based on a specific portion of the portion of each of the values, the specific portion being selected from a group consisting of: composition: Multiple bits, bytes, and text characters.

6. The method of claim 1, further comprising generating a plurality of different interleaving orderings based on priority queues containing a plurality of different subsets of the plurality of columns, wherein the priority queues are prioritized based on the efficiency of the applied interleaving orderings.

7. The method of claim 1, further comprising: include: generating the plurality of different interleaving orderings based on a queue including unapplied interleaving orderings; At least one selected from the group consisting of an empty subset of the plurality of columns and a first subset of the plurality of columns is appended to the queue, the first subset containing fewer columns than a second subset of the plurality of columns appended to the queue prior to the first subset of the plurality of columns.

8. The method of claim 1, further comprising performing an operation selected from the group consisting of shrinking a table containing the plurality of rows, executing a SELECT INTO statement, and executing an AS SELECT statement, wherein the performing of the operation results in a measure of the efficiency of the optimal interleaving ordering.

9. The method of claim 1, further comprising: include: detecting that a particular column has a configuration selected from the group consisting of a timestamp and a distinct value; Based on the detecting, the particular column is excluded from the plurality of columns.

10. The method of claim 1, wherein each memory region of the plurality of memory regions consists of a respective disjoint contiguous sequence of memory blocks.

11. The method according to claim 1, in: The plurality of storage areas include a first area and a second area; The first zone contains objects with a different count than the second zone; Object is selected from the group consisting of bytes, rows, and values.

12. One or more non-transitory computer readable media storing instructions which, when executed by one or more processors, cause the steps of any of claims 1-11 to be performed.

Images