Technology of Maintaining Statistics in Database Systems

Abstract

Techniques for collecting statistics in a database system are provided. The techniques involve collecting some statistics using "on-the-fly" techniques, some statistics using "high frequency" techniques, and still other statistics using "predictive" techniques. The technique used to collect each statistic is based at least in part on the overhead required to collect the statistic. For example, statistics with low overhead may be collected "on-the-fly" using the same process that is performing operations that affect the statistics, while statistics whose collection incurs greater overhead may be collected in the background using high frequency techniques while the database is online. Predictive techniques may be used for relatively high overhead statistics, which may be predicted based on historical data and the current value of a predictor statistic.

Description

Technical Field

[0001] The present invention relates to techniques for maintaining statistics within a database, and more particularly to techniques for maintaining statistics in a manner that balances the need for new statistics with the need to minimize the impact of statistics maintenance on database performance. Background Art

[0002] Often, the same database command can be executed in multiple ways, each producing the same correct result. Each of the various ways in which a database command can be executed has a corresponding "execution plan." The database component that selects which execution plan to execute for a given query is called an "optimizer."

[0003] Although all execution plans for a query should produce the same correct results, the time and resources required to execute execution plans for the same query can vary significantly. Therefore, choosing the best execution plan for a query can have a significant impact on the overall performance of the database system.

[0004] When selecting an execution plan for a query, the optimizer considers statistics maintained by the database server. Based on the statistics, the database server can, for example, determine whether using one join technique will be more efficient than another, and whether accessing information from a particular index is more efficient than accessing the same information directly from the underlying table. Because execution plan decisions are made based on statistics, the more accurate the statistics, the greater the likelihood that the optimizer will select the best execution plan.

[0005] Statistics that accurately reflect the current state of the database are called "real-time" statistics. Unfortunately, real-time statistics are not always available to the optimizer because maintaining real-time statistics can impose a significant overhead on database command processing. Users often have the option of performing a statistics collection process to "manually" collect the real-time statistics required by the optimizer. However, the overhead imposed by such manual statistics collection operations can degrade performance in a way that outweighs the performance benefits of choosing an execution plan based on real-time statistics.

[0006] To avoid such overhead, the optimizer often has to choose query plans based on "stale" statistics that do not reflect the current state of the database. The "stale" the statistics are, the less accurate they tend to be, and the less accurate the statistics are, the more likely the optimizer will choose a suboptimal query plan. For example, statistics that reflect the state of a table ten minutes ago will tend to be more accurate (and result in better query plan selection) than statistics that reflect the state of the same table ten days ago.

[0007] Many methods have been developed to efficiently collect statistics within a database system. For example, some such methods are described in U.S. Patent No. 9,582,525, U.S. Patent No. 9,798,772, U.S. Patent No. 10,073,885, and U.S. Patent Application No. 16 / 147,511, each of which is incorporated herein by reference. One method of collecting statistics involves waiting for a "quiet period" during which the database system is not executing database manipulation language (DML) commands that change the contents of the database. During the quiet period, the database server can perform a statistics collection process to refresh all statistics required by the optimizer. These statistics are then used by the optimizer until new statistics are collected during the next quiet period.

[0008] Such "quiet periods" are also called "maintenance windows" because they are used to perform maintenance operations that would slow down user applications if not performed during the quiet periods. Typically, maintenance windows are strictly enforced so that any maintenance operations (such as statistics collection) that are not completed within a maintenance window will be suspended until the next maintenance window.

[0009] When the "maintenance window" technique is used, the accuracy of the currently available statistics depends on how much time has passed since the most recent maintenance window. Typically, the "maintenance window" during which statistics are collected is overnight. Therefore, in the early morning hours, the statistics used by the optimizer of the database system may be relatively accurate. On the other hand, in the evening hours, the statistics used by the optimizer may be relatively inaccurate because the statistics do not take into account changes made by DML commands executed during the day. If the statistics collection process cannot be completed during a single maintenance window, the accuracy of the statistics is even lower.

[0010] The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section. BRIEF DESCRIPTION OF THE DRAWINGS

[0011] In the attached picture:

[0012] Figure 1 is a block diagram of a database system configured to store delta statistics in a volatile memory according to an embodiment;

[0013] Figure 2 is a flow chart illustrating steps for generating incremental statistics for MIN / MAX statistics according to an embodiment;

[0014] Figure 3is a diagram illustrating three types of statistical maintenance that may be used simultaneously in a database system according to an embodiment; and

[0015] Figure 4 is a block diagram of a computer system upon which embodiments of the invention may be implemented. DETAILED DESCRIPTION

[0016] In the following description, for the purpose of explanation, many specific details are set forth in order to provide a thorough understanding of the present invention. However, it will be 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 unnecessarily obscuring the present invention.

[0017] General Overview

[0018] This article describes techniques for collecting statistics in a database system. These techniques involve collecting some statistics using "on-the-fly" techniques, some statistics through "high-frequency" techniques, and other statistics using "predictive" techniques. The techniques used to collect each type of statistics are based at least in part on the overhead required to collect the statistics. For example, statistics with low overhead can be collected "on-the-fly", while statistics whose collection incurs greater overhead can be collected using high-frequency techniques. High-overhead statistics can be predicted based on other statistics collected on-the-fly. In addition, these techniques can be used in conjunction with other techniques (such as performing statistics collection processing during a maintenance window).

[0019] Just-in-time techniques involve storing statistics that reflect changes made by DML operations as they are executed and as part of the same process that executes the DML changes. According to one embodiment, rather than updating an on-disk copy of the statistics (typically found in the database's dictionary tables) to reflect statistical changes caused by DML operations, "delta statistics" are stored in an in-memory statistics repository.

[0020] In one embodiment, the in-memory incremental statistics generated in this manner are periodically flushed to disk reserved for incremental statistics. When the optimizer needs statistics to help choose which competing query plan to execute for a given query, the optimizer can call an API that retrieves the required incremental statistics from the in-memory repository, retrieves the corresponding incremental statistics from the dictionary table, and the corresponding base statistics, and merges them all to create "updated statistics" that the optimizer then uses to decide on an execution plan.

[0021] Maintaining instant statistics in this memory first provides "up-to-date" statistics without the overhead of updating the on-disk statistics in the dictionary tables in real time for each DML operation. This overhead can be significant because any number of concurrently executed DML operations will compete to obtain write locks on the relevant statistics in the dictionary tables. Since the updated statistics are up-to-date, the optimizer can more accurately choose the most efficient query plan. These statistics are flushed from memory to disk in a background job. Therefore, this overhead is invisible to the foreground application.

[0022] In one embodiment, incremental statistics are also stored on disk separately from base statistics. This provides isolation between the two statistics, allowing us to easily turn on and off the use of incremental statistics. This is not only beneficial for testing, but also provides control in the rare case where incremental statistics hurt query performance.

[0023] According to one embodiment, a "high frequency" collection technique is used to collect some statistics that are not maintained using just-in-time techniques. According to the high frequency collection technique, the database system does not wait for a maintenance window to collect statistics. Instead, the database server periodically initiates a background statistics collection process. The background statistics collection process is able to collect and store statistics regardless of other processes being executed in the foreground. In one embodiment, the statistics collection process is configured to start processing only when no other statistics collection process instances are active. In order to reduce the negative impact of the statistics collection process on the performance of concurrently executing foreground processes, the number of CPU cycles allocated to the statistics collection process is capped (for example, not more than 5% of the available CPU cycles).

[0024] Predicted statistics are statistics that are predicted based on historical information. For example, a database server may store a history of timestamps, row counts, and the number of distinct values ​​(NDV) for a column. If historical data indicates that the NDV for a column tends to increase by 50 for every 1000 rows added to a table, the database server may predict that the NDV count for the column will be 50 more than the NDV value stored at the most recent refresh time based on instantaneous statistics indicating that 1000 rows have been added to the table.

[0025] The forecast model is built on columns that have significant NDV changes in history, only for columns that will have values ​​in the forecast, because column NDV tends to change over time.

[0026] Predictive models are automatically maintained in the background, either during maintenance windows or during high-frequency statistical processing. In the model collection process, new models are built and existing models are refreshed. Models can be enabled or disabled depending on whether the quality of the model meets our criteria.

[0027] Before attempting to refresh an existing model, a check is made to determine if enough new statistical points have been collected since the last model creation time. Models that no longer meet quality criteria are disabled for use rather than discarded. In this way, the system avoids repeatedly building the same model for the same historical data for a column, which is wasteful.

[0028] Once the model is built, the system periodically extrapolates the NDV using the instantaneous statistics and stores the extrapolated results on disk. The extrapolated NDV is used along with other instantaneous statistics during query compile time. In other words, the extrapolation is done offline and does not incur overhead during query compile time.

[0029] Database statistics

[0030] The database system can maintain statistics at various levels. For example, the database system can maintain table-level statistics, column-level statistics, and index-level statistics. Table-level statistics include, for example, the number of rows in the table and the number of blocks in the table.

[0031] Column-level statistics include, for example, the minimum value in the column (MIN), the maximum value in the column (MAX), the average column width (in bytes), the cardinality of the column, and a histogram of the values ​​in the column.

[0032] Assuming that the index is implemented using a tree structure (e.g., B-tree or B+ tree, etc.), the index-level statistics may include: the number of leaf nodes in the index, the depth of the index tree, the total size of the index, the number of different keys, the clustering factor, etc. Other types of statistics may also be used for other types of indexes.

[0033] These are merely examples of the types of statistics that may be employed with the statistics collection techniques described herein. The techniques described herein are not limited to any particular statistic or set of statistics.

[0034] Collect real-time statistics

[0035] As described above, collecting instant statistics involves storing statistical information that reflects the changes made by DML operations as DML changes are executed. In one embodiment, the statistical information is stored in an in-memory statistics repository by the process that is executing the DML operation that affects the statistics. Figure 1 , which is a block diagram of a database system according to an embodiment. The database system includes a database server 100 having a volatile memory 102. Within the volatile memory 102, the database server 100 maintains an in-memory statistics repository 104 for those statistics collected at that moment in time.

[0036] The database server 100 is operatively coupled to one or more persistent storage devices, which are collectively represented by disks 160. The storage devices persistently store a database 120 managed by the database server 100. The database 120 includes tables 130 storing user data and dictionary tables 150 persistently storing statistics (including table statistics, column statistics, and index statistics) associated with the database 120. For example, the dictionary table 150 may store table statistics for the table 130, column statistics for each column in the table 130, and index statistics for the index 140 established on one or more columns of the table 130.

[0037] Because the instantaneous statistics are stored in response to the submission of the DML operation that affects the statistics, the statistics in the in-memory statistics repository 104 are very up-to-date. In addition, because the statistics are stored in the in-memory statistics repository 104 instead of being submitted to the dictionary table 150, the overhead associated with maintaining the statistics is greatly reduced. In addition, unlike the statistics in the dictionary table 150 that are typically maintained in a table-like structure, the statistics stored in the in-memory statistics repository 104 can be stored in a structure that optimizes access efficiency, such as a linked list, a hash table, or a combination thereof.

[0038] According to one embodiment, instantaneous statistics are maintained during data change operations such as INSERT, DELETE, UPDATE, and MERGE, regardless of whether the operation affects a single row or many rows, and regardless of whether the operation uses a conventional or direct path insertion method. (Direct path insertion techniques typically require locks on the entire table into which the rows are inserted, and the inserted rows will be stored on newly allocated blocks rather than being written to any allocated blocks of the table.) As described above, maintenance of instantaneous statistics is performed by the process that performs the data change operation that affects the statistics.

[0039] Currently, instant statistics are maintained only for the cheapest statistics. According to one embodiment, the database server maintains instant statistics for at least the following: table row count, table block count, column minimum and column maximum, number of null values, average column length of a column. This is only an example of statistics for which a database server may maintain instant statistics, and the techniques described herein are not limited to any particular set of instant statistics. In any case, the framework, i.e., the in-memory repository, the table on the table for incremental statistics, the refresh mechanism is common to other types of statistics.

[0040] For the purpose of illustration, the maintenance of instant statistics will be explained using an example of a DML command inserting ten rows into a table 130 having three columns c1, c2, and c3. Such an insert operation can affect the statistics of table 130, columns, and any index (e.g., index 140) associated with table 130, as will be explained below. It should also be assumed that dictionary table 150 contains accurate statistics since the most recent execution of the corresponding statistics collection process. The submission time of the most recent execution of the statistics collection process will be referred to as the "most recent refresh time" in this article. Typically, the most recent refresh time will be during the most recent maintenance window.

[0041] Real-time table statistics - row count

[0042] One table-level statistic affected by the insertion of ten rows into table 130 is the "row count" for table 130. According to one embodiment, database server 100 performs real-time tracking of DML changes (inserts, deletes, updates) that have occurred on each table since the most recent refresh time. For example, immediately after the most recent refresh time, dictionary table 150 may indicate that table 130 is 1000 rows, and in-memory statistics repository 104 may include a "delta" value of 0, indicating that no rows have been added to or deleted from table 130 since the most recent refresh time.

[0043] In response to inserting ten rows into table 130, database server 100 may update in-memory statistics repository 104 by incrementing the incremental row count value by 10 to indicate that ten rows have been added since the most recent refresh time. In one embodiment, access to each statistic within in-memory statistics repository 104 is governed by a latch. Thus, to update an in-memory statistic, a DML process will acquire a latch on the incremental row count value of table 130 within in-memory statistics repository 104, increment the incremental row count value by 10, and then release the latch on the incremental row count value.

[0044] When incremental row count changes are maintained in the in-memory statistics repository 104 in this manner, the live row count can be simply calculated by combining the DML changes (incremental row counts in the in-memory statistics repository 104) and the old row counts stored in the dictionary table 150 ("tab$.row_count"):

[0045] Real-time row count = tab$.row_count + delta-row-count (where delta-row-count is the number of inserts since the latest refresh time minus the number of deletes since the latest refresh time).

[0046] The instant incremental statistics are initially maintained in the in-memory statistics repository 104. In one embodiment, the incremental statistics in the in-memory statistics repository 104 are periodically refreshed to disk 160 via a background refresh process (e.g., every 15 minutes). Statistics refreshed to disk are stored in disk 160. Figure 1 142. After a refresh, the incremental statistics in the incremental row counts in the in-memory statistics repository 104 may be reset to zero. In such an embodiment, to obtain the latest row counts, the database server 100 combines information from three sources:

[0047] Real-time row count = (incremental row count in refresh statistics 142) + (row count in dictionary table 150) + (incremental row count in in-memory statistics repository 104).

[0048] For example, assume that the row count stored in the dictionary table is 150. That is, as of the most recent refresh time, table 130 has 150 rows. Assume that after the most recent refresh time, 100 rows have been inserted and 50 rows have been deleted. A record of the 100 insertions and 50 deletions (or incremental row count of 50) will be maintained in the in-memory statistics repository 104. At this point, information from the in-memory statistics repository 104 can be written to disk 160 as refresh statistics 142. In response to the in-memory statistics being refreshed to disk, the incremental row count statistics in the in-memory statistics repository 104 are reset. Additional DML operations may then result in 50 more rows being inserted, and 20 more rows being deleted (new incremental row count of 30). At this point in time:

[0049] The dictionary table 150 indicates that the row count of the table 130 is 150.

[0050] • Refresh statistics 142 indicate a delta row count of 50 (100 inserts and 50 deletes).

[0051] - The in-memory statistics repository 104 indicates a delta row count of 30 (50 inserts and 20 deletes).

[0052] By combining these statistics, database server 100 can generate an instantaneous row count of 150 + (100 - 50) + (50 - 20) = 230. The optimizer can then use the instantaneous row count 230 as the basis for selecting an execution plan for the query.

[0053] According to one embodiment, a copy of the refreshed incremental row count and a copy of the dictionary table 150 row count may be cached in volatile memory 102. In such an embodiment, generation of the real-time row count is faster because all three components thereof are available from volatile memory 102.

[0054] In yet another embodiment, the database server does not maintain incremental row counts in volatile memory. Instead, the row counts in dictionary table 150 are updated each time a DML operation that affects the row count is submitted. In such an embodiment, dictionary table 150 will always reflect the updated row counts. However, DML operations will have to contend for write permission to update the row counts within table 150.

[0055] In a cluster database system including multiple database servers, each database server can maintain its own incremental row count in its volatile memory. In such a cluster system, the database servers can periodically merge and synchronize the incremental row count values ​​in their memory.

[0056] For purposes of explanation, an embodiment has been described in which the incremental statistics are "reset" in volatile memory after being flushed to disk. However, in an alternative embodiment, flushing the statistics for a table to disk allows the volatile memory to be reused for other statistics. Thus, after the incremental statistics for table 130 are flushed to disk, the in-memory statistics repository 104 may no longer have any incremental statistics for table 130. Then, if / when another DML process performs an operation that affects the statistics for table 130, the new incremental statistics for table 130 are added to the in-memory statistics repository 104. Freeing up volatile memory after flushing statistics is desirable because there may be long periods of time during which no DML will affect a table, and it would be wasteful to continue consuming volatile memory for statistics for that table during those periods.

[0057] Combined Statistics

[0058] In the example given above, computing the immediate version of the row counts involves merging the incremental row counts from volatile memory 102, the incremental row counts flushed to disk (e.g., in refresh statistics 142), and the row counts in dictionary table 150. However, merging statistics also occurs when combining statistics from different DML processes.

[0059] For example, in a single instance of a database server, multiple DML processes may be executing concurrently, each performing inserts that affect the row counts of table 130 and the MIN / MAX values ​​of columns of table 130. In one embodiment, each concurrent DML process maintains its own delta statistics and, upon commit, causes those statistics to be merged into the server-wide delta statistics maintained in the in-memory statistics repository 104. As described above, access to the server-wide statistics maintained in the in-memory statistics repository 104 may be governed by latches.

[0060] In addition, multiple database server instances may be executing DML operations that affect the same statistics. In one embodiment, each of those database server instances maintains its own server-wide statistics in its respective volatile memory and periodically merges its server-wide statistics with the server-wide statistics of all other database server instances. For example, the merging of server-wide statistics may be performed by flushing the server-wide statistics of each server to disk 160 and then merging the statistics into dictionary table 150. In one embodiment, locks are used to serialize write access to each statistic maintained in dictionary table 150.

[0061] The way statistics are merged depends on the statistic in question. For example, merging the incremental MIN values ​​of a column (obtained from multiple DML processes in a server, or server-wide incremental MIN values ​​from multiple server instances) is performed by selecting the minimum of the incremental MIN values. Similarly, merging the incremental MAX values ​​of a column is performed by selecting the maximum of the incremental MAX values. On the other hand, merging incremental row count statistics for a table (obtained from multiple DML processes in a server, or server-wide incremental row counts from multiple server instances) involves summing the incremental row count statistics.

[0062] Real-time table statistics - block count

[0063] Another table-level statistic that database server 100 can track on-the-fly is the block count of table 130. On disk 160, the data for table 130 is stored in segments, and each segment has an associated extent map that records the total number of blocks consumed by the table. The extent map is modified as part of the DML processing that performs changes that result in new extents being added to the table. Therefore, to obtain the instantaneous block count for table 130, database server 100 simply reads the block count from the extent map for table 130 before the optimizer uses this information.

[0064] Real-time column statistics

[0065] Column-level statistics include minimum value, maximum value, number of distinct values ​​(NDV), number of null values, average column length, and histograms. These are much more difficult to maintain in real time than the row count and block count of a table. However, it is important to reduce the staleness of such statistics because significant errors can occur if the optimizer uses stale column-level statistics in the dictionary table 150.

[0066] For example, consider the following scenario where the optimizer is determining the execution plan for the query "SELECT name FROM emp WHERE age>80". Assume that the actual MAX value in the age column of the table emp is 85, but the stale MAX value recorded in the dictionary table 150 is 75. This may happen, for example, if a row with age=85 has been inserted since the latest refresh time. When the optimizer uses the stale MAX value of 75, the optimizer may conclude that no row will satisfy the condition "age>80", and based on this wrong conclusion, the performance of processing the query may be affected because incorrect statistics are used.

[0067] Real-time column statistics for MIN / MAX

[0068] According to an embodiment, to avoid errors caused by the optimizer using stale column-level statistics, instant column-level statistics are calculated for the minimum and maximum values ​​of the columns. Thus, for tables that have not had statistics refreshed recently, the optimizer can access the latest minimum and maximum values ​​for each column. Among other things, access to the real-time minimum and maximum values ​​for each column helps the optimizer accurately identify and handle out-of-range queries.

[0069] Similar to instant table statistics tracking, instant MIN / MAX tracking involves performing additional work whenever a DML operation occurs. In one embodiment, the work performed depends on whether the DML operation is performing an insert, delete, or both (where updates are considered deletes followed by inserts).

[0070] To enable instant MIN / MAX tracking for insert operations, a "delta" minimum value and a "delta" maximum value for each column are maintained in the in-memory statistics repository 104. After a statistics collection operation is performed on the MIN / MAX values ​​of a column (e.g., during a maintenance window), the dictionary table 150 is updated with the MIN / MAX for each column and the delta MIN / MAX values ​​in the in-memory statistics repository 104 are initialized. According to one embodiment, the delta MIN / MAX is initialized to zero, rather than the MIN / MAX values ​​stored in the dictionary table, and the in-memory repository only tracks the MIN / MAX of incremental data changes performed since the most recent refresh time.

[0071] In an alternative embodiment, the incremental MIN / MAX is initialized to the MIN / MAX value stored in the dictionary table. For example, assume that table 130 has columns "age" and "salary". Assume further that dictionary table 150 indicates that the MIN / MAX value for "age" is 15 / 80, and the MIN / MAX value for "salary" is 20,000 / 1,000,000. In these cases, the incremental MIN / MAX values ​​stored in the in-memory statistics repository 104 are initialized to 15 / 80 for the age column and to 20,000 / 1,000,000 for the salary column.

[0072] Assume that a DML operation is executed in which ten rows are inserted into table 130 immediately after the most recent refresh time at which those statistics are stored in dictionary table 150. The process of executing the DML also performs statistics maintenance by:

[0073] • Set the incremental minimum age in the in-memory statistics repository 104 to the lesser of: (a) the current incremental minimum age, and (b) the smallest age value among the ten newly inserted rows.

[0074] • Set the delta max age in the in-memory statistics repository 104 to the greater of: (a) the current delta max age, and (b) the largest age value among the ten newly inserted rows.

[0075] • Set the incremental minimum salary in the in-memory statistics repository 104 to the lesser of: (a) the current incremental minimum salary, and (b) the lowest salary value among the ten newly inserted rows.

[0076] • Set the incremental maximum salary in the in-memory statistics repository 104 to the greater of: (a) the current incremental maximum salary, and (b) the highest salary value in the ten newly inserted rows.

[0077] Therefore, assuming that the maximum age inserted among the 10 inserted rows is 85, the DML performing the insert operation will update the incremental maximum age value stored in the in-memory statistics repository 104 to 85, because 85 is greater than the current incremental age maximum value of 80.

[0078] Similarly, if the minimum age inserted in the 10 inserted rows is 13, then the DML operation executing the insert will update the incremental minimum age value stored in the in-memory statistics repository 104 to 13, since it is less than the current incremental age maximum value of 15. The DML operation executing the insert will also update the incremental salary MIN / MAX values ​​in a similar manner.

[0079] Since the in-memory statistics repository has only incremental MIN / MAX, the optimizer will compute the latest statistics on the fly by merging the in-memory MIN / MAX and dictionary tables and use these statistics to process the query.

[0080] Use sampling to reduce the overhead of maintaining real-time statistics

[0081] Of course, maintaining column-level statistics (such as MIN and MAX for each column) on-the-fly requires time and memory / disk space. In addition, since the statistics collection overhead is performed by the foreground DML processing that affects the statistics, the statistics collection overhead will affect the performance of user applications and may reduce the user experience. According to one embodiment, in order to minimize the work done in the foreground, a technique is provided to implement a live MIN / MAX based on a sample instead of an exact MIN / MAX. That is, in order to reduce the overhead required to maintain the instant MIN / MAX column statistics, only a subset of the inserted rows are considered when determining the MIN / MAX values ​​inserted during a DML operation.

[0082] For example, assume that a DML operation inserts 1000 rows. If sampling is not performed, then the values ​​inserted into each column of each of the 1000 rows will need to be analyzed to determine the MIN / MAX values ​​inserted into each column by the DML operation. According to one embodiment, instead of analyzing the values ​​inserted into each of the 1000 rows, the database server 100 performs a random sampling of the 1000 rows. For example, the random sampling may select only 100 rows from the 1000 rows. Determining the MIN / MAX values ​​for each column of the sampled 100 rows will require much less overhead than determining the MIN / MAX values ​​for all 1000 inserted rows.

[0083] refer to Figure 2 , which is a flow chart illustrating steps performed by the database server 100 for maintaining instant per-column MIN / MAX statistics according to an embodiment. At step 200, the database server receives a DML command that inserts a row (either through an INSERT operation or as part of an UPDATE operation). At step 202, a sample is created from the rows that will be inserted during the DML command. For example, a sample may be created by randomly selecting one row out of every 100 rows to be inserted by the DML command. The actual sampling technique may vary, and the statistics maintenance techniques described herein are not limited to any particular sampling technique.

[0084] At step 204, the database server 100 determines the sample MIN / MAX values ​​for each column of each row in the sample. Finally, at step 206, the sample MIN / MAX values ​​derived in this manner are combined with the incremental MIN / MAX values ​​stored in the statistics repository 104 in memory. In one embodiment, Figure 2The steps shown in are performed by the same process as if the DML operation was inserting values. However, because the MIN / MAX values ​​are determined only for a sample, the overhead incurred by the process performing the statistics maintenance is much less than if all inserted rows were processed.

[0085] Allowable inaccuracy

[0086] When generating instant incremental statistics based on sampling rather than all inserted values, the resulting statistics may not be accurate. For example, suppose the current incremental MAX age is 80, the MAX age in the sample is 85, and the MAX age in all inserted rows is 90. In this case, even if a row with age=90 is inserted into the age column, the incremental MAX age will be updated to 85.

[0087] Another situation that can lead to inaccurate statistics is the use of insert-only incremental statistics. Insert-only incremental statistics are incremental statistics that only consider the impact of inserts on the statistics, even though the DML operation may involve more than just inserts.

[0088] In one embodiment, rather than incurring additional overhead to ensure that incremental MIN / MAX statistics are accurate after updates and / or deletes, incremental statistics for MIN / MAX are allowed to become inaccurate. That is, only incremental MIN / MAX statistics are updated to account for only inserts made by DML operations. Even though insert-only incremental MIN / MAX statistics may not be accurate, insert-only incremental MIN / MAX statistics will be more accurate than statistics stored in dictionary table 150 that do not account for any changes since the most recent refresh time. Furthermore, for columns where values ​​must increase (such as a date column for a sales table), accounting for only inserted values ​​may still result in accurate MAX statistics.

[0089] High-frequency statistics collection

[0090] Unlike immediate statistics collection, high-frequency statistics collection does not cause the same process that is performing DML operations that affect statistics to perform any additional work to maintain or update statistics. Instead, high-frequency statistics collection involves performing the statistics collection process in the background while allowing DML operations to continue to execute in the foreground process.

[0091] Because the statistics collection process is being executed in the background, the CPU resources available for the statistics collection process may be limited. For example, in one embodiment, the statistics collection process is allowed to consume no more than 5% of the CPU clock cycles available to the database server 100.

[0092] The statistics collection process is considered "high frequency" because the statistics collection process is initiated much more frequently than a "maintenance window". For example, in some systems, a maintenance window may only occur once every 24 hours. In contrast, a high frequency statistics collection process may be initiated every 15 minutes.

[0093] The operations performed by the statistics collection process may take longer to execute than the interval that triggered the execution of the statistics collection process. For example, a high-frequency statistics collection process may take 20 minutes to complete, even if the interval at which the process is executed is 15 minutes. Therefore, according to one embodiment, the database server 100 is designed to only start a new statistics collection process for a particular statistic or set of statistics if no other statistics collection process instance is active for the same statistic or set of statistics.

[0094] For example, if a MIN / MAX statistics collection process for a particular table is initiated at 1:00 pm, but that process has not completed by 1:15, then a new MIN / MAX statistics collection process is not initiated for that particular table at 1:15. Instead, the currently active MIN / MAX statistics collection process is allowed to continue without competing for resources with other statistics collection processes associated with the same column MIN / MAX statistics.

[0095] Adaptive frequency statistics collection

[0096] In the example given above, high-frequency statistics are collected at fixed intervals. However, collecting statistics at fixed intervals may be too frequent in some cases and not frequent enough in other cases. If collected too frequently, the overhead of statistics collection can affect the performance of the system. If collected not frequently enough, the optimizer will not have new statistics to make the best execution plan choice.

[0097] It can be expected that the amount of DML a table will experience will increase dramatically during the ramp-up phase (when the table is initially populated, such as during the initial migration of a table), and gradually become less and less active. When it is in the ramp-up phase, statistics should be collected very frequently to keep up with data changes. When the size of the table reaches a certain order of magnitude, the same frequency will not apply.

[0098] Therefore, according to one embodiment, for high frequency background statistics collection, instead of collecting statistics at a fixed frequency, an adaptive frequency depending on the actual workload is used. Only when stale statistics for a table become important, the optimizer is likely to be misled by the statistics. Therefore, in one embodiment, background statistics collection is invoked only in those cases. Therefore, in one embodiment, statistics maintenance is a demand-driven operation that requires actions to be taken both in the foreground and in the background, as will be described in detail below.

[0099] Queue for statistics collection

[0100] According to one embodiment, the database server 100 maintains a queue for statistics collection. Each entry in the statistics collection queue identifies a database structure, such as a table, partition, or index, for which it is time to collect statistics. The condition that triggers the insertion of an entry in the queue for statistics collection is that the staleness of the database object exceeds a staleness threshold (e.g., 10%).

[0101] In one embodiment, as a foreground action, the database server 100 detects that the table exceeds the percentage staleness threshold and places the entries of the target table into a queue awaiting background statistics collection. In a clustered database environment, the DML monitoring information in the volatile memory of each server node may not be sufficient to determine staleness. Therefore, in such an embodiment, the statistical information in the volatile memory of each server node is flushed to disk for staleness determination.

[0102] According to one embodiment, the database server creates a job that is woken up periodically, for example, every 1 minute, to refresh the DML monitoring information to disk. When refreshing the statistics of a specific table to disk, the stale percentage of the specific table is calculated. If it exceeds the threshold, the database server will place an entry for the specific table and put the entry of the table into the waiting queue.

[0103] In an alternative embodiment, a mechanism continuously checks if the target queue is empty. If the queue is empty, then the DML monitoring information for the various database objects that have undergone DML is flushed to disk. When the statistics for a particular table are flushed to disk, the stale percentage for that particular table is calculated. If a threshold is exceeded, then the database server 100 places the entry for that particular table into the queue for statistics collection.

[0104] In yet another embodiment, during the commit of a DML operation, the database server 100 calculates staleness on the fly to add objects involved in the current transaction to a queue for statistics collection if their staleness exceeds a 10% threshold. The benefit of DML-based staleness determination is that it is completely drift-driven, not time-driven. However, since DML is executed in the foreground, staleness determination is also performed in the foreground. As an action taken in the foreground, its overhead should be thoroughly minimized to reduce the impact on user applications.

[0105] Maintaining queues for statistics collection

[0106] The queue for statistics collection can be shared by multiple different database server instances. According to one embodiment, each database server instance can add an entry to the queue, and each database server instance can process the entry at the head of the queue.

[0107] To process the entry at the head of the queue, the database server instance reads the database object identified in the entry, marks the entry as "in progress", and initiates an appropriate statistics collection operation to collect statistics for the database object. After completing the statistics collection operation, the database server instance removes the entry from the queue (or marks the entry as "complete") and updates the statistics for the database object in the dictionary table 150.

[0108] Predictive statistics generation

[0109] As previously mentioned, some statistics may be too expensive to maintain in real time. According to one embodiment, a prediction method is used to predict approximate real-time values ​​of those statistics.

[0110] For example, in one embodiment, the number of distinct values ​​(NDV) for a column is not maintained in real time, because to find out whether the value of the column in the inserted row is new (NDV should be incremented by 1) or old (NDV remains unchanged), the value must be compared with the value of the column in the rows already existing in the table. However, in many cases, the number of distinct values ​​is related to other types of statistics, such as row counts, timestamps. If related statistics are maintained in real time, they can be used to derive the current NDV.

[0111] According to one embodiment, the database server generates instantaneous predictions of certain statistics as follows:

[0112] Predictor statistics are maintained in real time (e.g. timestamps, row counts)

[0113] Statistics collection jobs collect and store historical values ​​of target statistics and predictor statistics. These jobs also build prediction models on those columns that exhibit strong correlations between predictor statistics and target statistics, and refresh the models when the correlation pattern changes. Statistics collection jobs can occur during maintenance windows, or can occur as part of high-frequency statistics collection. Given the predictor statistics maintained in real time, the system uses the prediction model created for this column (if such a model exists) to predict the current target statistic.

[0114] For example, assume that the timestamps and row counts of a table of N points and the NDV have been recorded in the past (e.g., each of the last 100 days). This historical information can be used to create a prediction model that takes the statistics collected in real time (e.g., timestamps, row counts) as input and outputs a predicted NDV value. The prediction mechanism can be, for example, a machine learning engine. In an embodiment of a machine learning engine, the machine learning engine can be trained by feeding it N records of (timestamps, row counts) and the corresponding previously recorded NDV values.

[0115] Once the machine learning engine is trained in this manner, the database server 100 can cause the machine learning engine to generate an instant prediction of NDV for the column by (a) collecting timestamp values ​​and table row counts on the fly using the techniques described above, and (b) feeding these values ​​to the trained machine learning system. While not guaranteed to be accurate, the NDV values ​​generated in this manner are likely to be much more accurate than the stale NDV stored in the dictionary table 150, especially if many DMLs have been run on the column since the last refresh of the NDV statistics for the column.

[0116] Multi-layer statistics collection method

[0117] As described above, different statistics present different statistical collection challenges. For example, some statistics may be maintained with much less overhead than other statistics. In addition, some statistics may be more accurately predicted than other statistics. Therefore, according to one embodiment, different statistical collection methods for different statistics are provided for a database system.

[0118] refer to Figure 3 , which is a flow chart illustrating how statistics are maintained by a database implementing a multi-layered statistics collection method according to an embodiment. Step 300 indicates that the manner in which statistics are collected is based on the type of statistics. For statistics such as table row counts, whose collection incurs relatively little overhead, ad hoc statistics collection is performed, as shown at step 302. As explained at step 304, during ad hoc statistics collection, processing that executes DML that affects statistics also performs operations for statistics collection. For example, for table row counts, the operation may involve updating an incremental row count in memory based on how many rows were inserted by the DML in question.

[0119] For statistics that require more overhead, a high frequency background statistics collection operation may be performed (step 312). As shown in step 314, the background process that performs the high frequency statistics collection may be initiated periodically or based on the staleness of the corresponding database objects. In the latter case, the adaptive frequency may involve determining the staleness of the statistics for each database object. For those objects that exceed the specified staleness, an entry is added to a queue for statistics collection. The background process may read the record at the head of the queue and collect statistics for the database object associated with the entry.

[0120] Finally, for statistics that require a lot of overhead to collect in real time, but can be predicted with reasonable accuracy based on other statistics, a statistical prediction can be used (step 322). As shown in step 324, generating an immediate prediction of the statistic can involve feeding the immediate value of the predictor statistic to a trained machine learning engine. Based on the immediate value of the predictor statistic, the machine learning engine outputs a predicted value for the predicted statistic. For example, a trained machine learning engine can output the NDV value of a column based on the MIN / MAX value of the column and the current row count.

[0121] The complementary relationship between these three statistical maintenance techniques is as follows:

[0122] Maintain minimal statistics such as table row counts, block counts, number of column MIN / MAX / NULL values, average column length via sample-based ad hoc statistics collection;

[0123] NDVs that cannot be maintained in real time will be addressed by statistical forecasts.

[0124] Since both real-time statistics collection and statistics forecasting are not 100% accurate, background high-frequency statistics collection will correct the statistics when necessary. Note that the statistics collected via high-frequency statistics collection are accurate.

[0125] cloud computing

[0126] 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 rapid provisioning and release of resources with minimal management effort or service provider interaction.

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

[0128] In general, the cloud computing model enables some of those responsibilities that were previously provided by an organization's own information technology department to be delivered as a service layer within the cloud environment for use by consumers (either within the organization or outside the organization, depending on the public / private nature of the cloud). 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), where consumers use software applications running on the cloud infrastructure, while the SaaS provider manages or controls the underlying cloud infrastructure and applications. Platform as a Service (PaaS), where consumers can use software programming languages ​​and development tools supported by the PaaS provider to develop, deploy and control their own applications, while the PaaS provider manages or controls other aspects of the cloud environment (i.e., everything below the runtime execution environment). Infrastructure as a Service (IaaS), where consumers can deploy and run arbitrary software applications, and / or provision processing, storage, network 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), in which the consumer uses a database server or database management system running on a cloud infrastructure, while the DbaaS provider manages or controls the underlying cloud infrastructure, applications, and servers, including one or more database servers.

[0129] Statistical Search

[0130] When the database server needs statistics (e.g., to choose among competing execution plans for a query), the database server can retrieve the statistics in a variety of ways. For example, instantaneous incremental statistics can be periodically merged into the dictionary table, and the database server can be designed to always obtain statistics from the dictionary table. On the other hand, the database server can be designed to check the in-memory statistics repository 104 for the most recently generated instantaneous statistics, and only go to the dictionary table 150 if such statistics are not in the in-memory statistics repository 104. In yet another embodiment, the database server can retrieve older statistics from the dictionary table 150\incremental statistics from the in-memory statistics repository 104, and merge the two to provide instantaneous statistics.

[0131] In yet another embodiment, the database server is isolated from the manner in which the instant statistics are generated by providing an API through which the database server can request statistics. In response to a call to the API, the statistics generation process can generate the instant statistics in any of a variety of ways. For example, depending on the actual statistics requested, the statistics generation process can:

[0132] Get statistics from dictionary table 150

[0133] Get incremental statistics from the in-memory statistics repository 104

[0134] Combine the incremental statistics from the in-memory statistics repository 104 with the statistics from the dictionary table 150

[0135] Obtain prediction statistics from the prediction mechanism

[0136] The statistics generation techniques described herein are not limited to any particular mechanism for providing desired statistics to a database server.

[0137] Hardware Overview

[0138] 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.

[0139] 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.

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

[0141] 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.

[0142] 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., x) and a second axis (e.g., y), which allows the device to specify a position in a plane.

[0143] 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 described techniques 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.

[0144] 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.

[0145] 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.

[0146] 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.

[0147] 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.

[0148] 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.

[0149] 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 send the requested code for an application program through Internet 428, ISP 426, local network 422, and communication interface 418.

[0150] 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.

[0151] In the foregoing specification, embodiments of the present invention have been described with reference to numerous specific details, which may vary from implementation to implementation. Accordingly, the specification and drawings should be regarded as illustrative rather than restrictive. The sole and exclusive indicator of the scope of the invention, and what the applicants intend as the scope of the invention, is the literal and equivalent range of the set of claims issuing from this application in the specific form in which such claims are issued, including any subsequent corrections.

Claims

1. A method performed by one or more computing devices, comprising: storing statistics reflecting the state of the database on a persistent storage device; Receiving a data manipulation language (DML) command at a database server; In response to the DML command: Make one or more changes to data stored in the database; as well as generating one or more incremental statistics reflecting how the one or more changes affect the statistics; as well as storing the one or more incremental statistics in a volatile memory of a database server, generating one or more instantaneous statistics based on the statistics on the persistent storage device and the one or more incremental statistics; as well as selecting a query plan from a plurality of candidate query plans for a subsequent database command based at least in part on the instantaneous statistics; Wherein generating one or more incremental statistics comprises generating incremental statistics corresponding to a change in one of the following: The count of rows in the table; The count of blocks in the table; The maximum value in a column of a table; The minimum value of the table column; the number of null values ​​in the table's columns; or The average column length of the table's columns.

2. The method of claim 1 , wherein generating the one or more incremental statistics comprises: Select a sample of items inserted by the DML command; as well as The one or more incremental statistics are generated based on a sample of the items.

3. The method of claim 1, wherein: The statistics reflecting the state of the database are stored in one or more dictionary tables in the persistent storage; and The status is the latest refresh time of the statistics.

4. The method of claim 1, wherein: The one or more instantaneous statistics correspond to a first subset of statistics stored on the persistent storage device; The method also includes, for each statistic in the second subset of statistics stored on the persistent storage device, refreshing the statistics by causing the database server to invoke a statistics collection operation performed in the background.

5. The method of claim 4, wherein the statistics collection operation performed in the background is invoked by the database server based on the passage of a fixed time interval.

6. The method of claim 5, wherein: The database server does not invoke another instance of the background-executed statistics collection operation when a previous invocation of the background-executed statistics collection operation has not completed after a fixed time interval has elapsed.

7. The method of claim 4, wherein the statistics collection operation performed in the background is invoked by the database server based on a second subset of statistics exceeding a staleness threshold with respect to a database object.

8. The method of claim 7, further comprising: determining, by the foreground process, that a second subset of the statistics of the database objects exceeds a staleness threshold; adding an entry of the database object to a queue for statistics collection; as well as A background process is invoked that performs a statistics gathering operation based on the entries in the queue to gather a second subset of statistics for the database objects.

9. The method of claim 1, wherein: The one or more instantaneous statistics include one or more predictor statistics for a corresponding statistic; and The method also includes generating an instantaneous predicted value for the corresponding statistic using a prediction mechanism based on: generating values ​​for the one or more predictor statistics on the fly; as well as The one or more predictor statistics and a history of values ​​of the corresponding statistics.

10. The method of claim 9, wherein the prediction mechanism is a trained machine learning engine.

11. The method of claim 9, wherein the corresponding statistic is the number of distinct values ​​of a column of a table.

12. The method of claim 10, further comprising the steps of: The trained machine learning engine is trained to predict the corresponding statistic by training the trained machine learning engine with the one or more predictor statistics and a history of values ​​of the corresponding statistic.

13. The method of claim 11, further comprising: Select the column as the column for which the number of distinct values ​​is to be predicted; Wherein selecting the column is based at least in part on a degree to which a number of distinct values ​​for the column tends to change over time as indicated by historical data associated with the column.

14. The method of claim 9, wherein: The prediction mechanism uses a prediction model; and The method also includes maintaining the predictive model using background processing or during maintenance windows or using high frequency statistical processing.

15. The method of claim 9, wherein: The prediction mechanism uses a prediction model; and The method also includes, once the predictive model is created, refreshing the model only when sufficient new statistical points have been collected since a last model creation time associated with the predictive model.

16. The method of claim 9, wherein: The prediction mechanism uses a prediction model to predict the number of distinct values ​​of a column; and The method further comprises: Before receiving a query: using the prediction model to predict the number of distinct values ​​of the column; storing a predicted number of distinct values ​​for the column; and After receiving the query, the query is processed using the stored predicted number of distinct values ​​for the column.

17. One or more non-transitory computer-readable media storing instructions that, when executed by one or more computing devices, cause the method of any one of claims 1 to 16 to be performed.

18. A computer program product comprising instructions which, when executed by one or more computing devices, cause the method of any one of claims 1 to 16 to be performed.

19. A computer system comprising one or more computing devices, the one or more computing devices being configured to execute the method of any one of claims 1 to 16.

Images