Autonomous partitioned database table

Abstract

Disclosed is an improved method of performing automatic partitioning without requiring any expertise on the part of the user. A three-stage processing pipeline is provided to generate candidate partitioning schemes, evaluate the candidate partitions using empty real table structures, and then execute the selected scheme using production data for evaluation. In addition, an improved method of performing automatic range partitioning is described in which the inventive concept enables range partitioning without imposing these implicit constraints on the partition key column.

Description

TECHNICAL FIELD

[0001] The present disclosure relates to methods, computer program products, and computer systems for implementing partitioning in a data processing system. BACKGROUND

[0002] Data processing systems, such as database management systems (DBMSs) and relational database management systems (RDBMSs), allow applications running on clients (e.g., workstations, personal computers, or remote computing terminals) to access data stored in databases located at one or more database servers. The database servers can be interconnected by a network. The network can also connect one or more clients to the database servers, allowing the clients to submit queries and requests via the network to perform operations on the data stored at the database servers.

[0003] Tables stored in a database can be divided or“partitioned” into smaller portions or“partitions.” Tables can be partitioned according to various partitioning strategies, such as range partitioning, hash partitioning, and list partitioning. For a given table, each row can be assigned to a single partition based on a table partitioning key, which corresponds to one or more columns, that are used to determine the partition to which each row will be assigned.

[0004] Partitioning large tables provides several advantages, including improved query performance. Executing queries directed to a partitioned table on a partition-by-partition basis (i.e., on a partition-by-partition basis) can be faster than executing the same query directed to a table that is not partitioned, as partition-by-partition execution limits the amount of data to be examined or operated on, allowing for improved query performance via parallel execution and / or partition pruning. For example, to execute a query directed to a partitioned table, a query coordinator process can assign different partitions of the table to each of a plurality of slave processes. In this example, the slave processes can execute the query in parallel on the partitions assigned to them and return the results to the query coordination process, which coordinates the results and sends the results back to the client that initiated the query. Partition pruning can also improve query performance for queries directed to a partitioned table by removing or“pruning” unneeded partitions (e.g., partitions containing partition keys that are not relevant to the query) from processing, further reducing the amount of data to be examined or operated on.

[0005] Additional benefits of partitioning large tables include enhanced manageability and availability of large amounts of data. For example, instead of performing maintenance operations on a large table, a database administrator can perform maintenance operations on each partition, which is often a more manageable block of data to work on. In this example, by performing maintenance operations in small batches, scheduled downtime can be reduced, thereby enhancing data availability. Additionally, in cases where different partitions of a table are stored in separate tablespaces on different storage tiers, partitioning can also enhance data availability. In this case, if one partition becomes unavailable, the other partitions can still be available, allowing queries to continue to be processed on those partitions.

[0006] The problem addressed by the present disclosure is that while partitioning is widely used in database systems to improve performance, it is difficult for the average user to implement partitioning in an effective and optimal manner. If the user makes mistakes in partitioning implementation, for example, creating too many partitions, too few partitions, using the wrong partitioning scheme, or using the wrong partitioning key / criteria, then inefficiencies and waste of computing resources can result. In fact, current solutions mostly rely on the existence of expert users who take on the task of manually partitioning tables, with the hope that the expert users are sufficiently "expert" to generate usable and harmless partitioning methods. This reliance on the existence of expert users is not sufficient to make the solution universally applicable, and thus there is a need for an improved method for implementing partitioning of data processing systems. SUMMARY

[0007] Embodiments of the present invention provide a method, computer program product, and computer system for implementing partitioning in a data processing system without requiring any expertise on the part of the user. A three-stage processing pipeline is provided to generate candidate partitioning schemes, evaluate the candidate partitions using an empty real table structure, and then implement the selected scheme using production data for evaluation.

[0008] With embodiments of the present application, an automatic partitioner operates in a system to effect automatic partitioning of a non-partitioned table. In particular, the automatic partitioner receives certain information about the non-partitioned table. For example, some embodiments gather information such as data about the table itself (e.g., the size of the table, table schema information, and table statistics such as cardinality statistics), information about the workload applied against the table (e.g., SQL statements applied to the table as part of a user workload and an explanation of the execution plan for the SQL statements), and information about performance metrics captured when running the workload against the non-partitioned table. The automatic partitioner uses the gathered information to generate a plurality of candidate partitioning schemes. Each of the plurality of candidate partitioning schemes is a different approach to potentially partitioning the non-partitioned table into a partitioned table. The candidate partitioning schemes can encompass different partitioning approaches, including different types of partitioning, and partitioning based on different columns within a given table (e.g., the partition key(s)). A partitioning scheme selector operates to select from the plurality of candidate partitioning schemes to select one of the candidate schemes that is projected to provide the best system performance. Note that at this stage, the candidate partitioning schemes have not yet been implemented as tables with full population from all production data from the table in the main database system. Instead, the candidate schemes are implemented at this stage as real tables that are currently empty, with synthetic statistics used to project and estimate the expected performance of each candidate scheme. The selected partitioning scheme will be implemented in the database system in actuality, with a new partitioned table created and populated with real production data. The actual user workload will be run against the populated partitioned table to collect real-world execution results from the application of the selected partitioning scheme, and these results are compared to the performance of the prior non-partitioned database table. The selected partitioning scheme can then be verified as successful, or dropped out of if unsuccessful. In one embodiment, if successful, the partition is applied to the user's table, and if rejected, the "drop out" process is to discard the new table.

[0009] Any number of different partitioning methods can be applied to partition a database object / table, such as: (a) range partitioning, (b) hash partitioning, or (c) list partitioning. Each of these methods is used as a data distribution method to control how data is placed into individual partitions, with each row in a partitioned table being explicitly assigned to a single partition.

[0010] The method of generating multiple candidate partitioning schemes according to some embodiments of the present application can include identifying one or more tables to evaluate for partitioning, running an "explain plan", filtering candidates, identifying possible candidates, scoring candidates, and / or ranking and selecting candidates. Any suitable approach can be taken to identify tables for evaluation at this stage. For example, one approach is to exclude any tables that have been partitioned in a particular manner by a user. Another approach is to select the table(s) that have the largest size (e.g., based on number of rows / data volume) that are not currently partitioned. In some embodiments, the cost of implementing a partition (e.g., the cost of moving data within the system to implement a given partitioning scheme) can be balanced against the potential benefit of implementing a new partitioning scheme in order to select a table for automatic partitioning processing. Further, any suitable approach can be taken to initiate partitioning processing to identify tables for automatic partitioning. One possible approach is to have a user manually identify tables for partitioning. Another approach is to automatically discern and automatically trigger partitioning processing. For example, for tables that are not currently partitioned, the processing can be triggered by recognizing a table as reaching a specified threshold size, receiving a threshold number of queries in the workload for the table, or any other factor or combination of factors that indicates that the table can potentially benefit from partitioning. For tables that are already partitioned, the processing can be triggered if the number of partitions for the table reaches a threshold number that is considered excessive, if the size of an individual partition reaches a threshold size, and / or any other factor or combination of factors that indicates that the table can potentially benefit from repartitioning. Further, the database schema can be configured to establish black list and / or white list parameters to specify particular tables that the automatic partitioning processing should or should not address. Queries within the user's workload that actually reference the selected table can be identified, for example, to identify, for example, (a) a set of SQL statements, (b) any associated execution context, such as user mode, application module name(s) and action(s), a list of bind values, and a SQL compilation environment for the associated cursor, (c) associated basic execution statistics, such as elapsed time, CPU time, buffer gets, disk reads, rows processed, cursor fetches, execution count, full execution count, optimizer cost, and command type, and / or (d) an associated execution plan and row source statistics for each SQL statement. Any queries that do not belong to the selected table are not considered at this time. For each query that is related to the selected table, the execution plan for the query is obtained and reviewed, for example, to identify some or all of the following information: (a) the order of tables referenced by the statement, (b) the access method for each table mentioned in the statement, (c) the join method for tables affected by join operations in the statement, and / or (d) data operations, such as filtering, ordering, or aggregation.In addition to the row source tree, the schedule can also contain information about one or more of the following: (a) optimization, such as cost and cardinality of each operation, (b) partitioning, such as a set of partitions to access, and / or (c) parallel execution, such as a distribution method for join inputs. In some embodiments, just resolving the output of the actions can provide enough information without going through the entire explain plan process.

[0011] Columns frequently used in predicates are good candidates for partition keys. This is because columns used in highly selective predicates are typically the best candidates because they can provide the best performance improvement through proper partitioning. However, it is often required to do engineering work to get predicate selectivity, especially for join predicates. Thus, some embodiments of the present invention identify and generate predicate counts within SQL statements, where the predicate counts are then used to find candidate partition keys. Note that base data of database objects within a database can also be used in embodiments of the present invention, where the base information is useful in determining which type of partitioning should be applied to a particular column. A set of rules can be used to perform an initial filtering step to remove suboptimal candidates. A set of candidate partitioning schemes can be identified based at least on the predicates and base information. For each column, the method in some embodiments considers several possible partitioning methods, including interval / range partitioning, hash partitioning, and list partitioning. The candidate partitioning schemes are generated based on at least two factors, including (a) the set of predicates in the query, and (b) the number of distinct values within a given column. A score is identified for each distinct candidate partitioning scheme, where the candidates are ranked according to their respective scores, and the candidate list is pruned such that only a subset of the candidates proceed to the next stage for more detailed analysis and consideration. With respect to scoring, one approach is to see how many types of predicates are associated with a given column within a table, and use that information to score any associated partitioning schemes based on those predicate types that are based on the specified column. This type of calculation can indicate the level of efficiency gained by implementing certain types of partitioning for a column.

[0012] Based on their respective scores, the top n partitioning schemes are evaluated at the evaluation phase. For each candidate partitioning scheme, the process creates an empty partitioned table that has columns, indexes, and constraints cloned from the original table. Since an empty table is created, this means that no data segments are allocated for the empty table. The method materializes the partitioning for interval and auto list candidates to match the data in the original table. Next, the synthetic statistics are computed for the empty partitioned table, its partitions, its columns, and / or its indexes. Since the process works with the empty partitioned table, this means that while these are real tables that have been created and exist in the database data dictionary, all the actual data from the original non-partitioned table is not copied into these tables at this stage. The synthetic statistics are computed to provide data for the optimizer to act upon. Additional filtering actions can occur to remove any candidates with sub-optimal results that can be identified from the synthetic statistics computation process from consideration. At this point, the user workload can be compiled and the performance of the workload estimated by the optimizer against the partitioned table with the synthetic statistics. A mechanism can be provided to automatically translate one table name to another table name during SQL compilation. This mechanism is set to translate the original table name to the name of the empty partitioned table. Then, the SQL performance analysis is used to compile each query in the workload. I / O cost calculations can be generated during the SQL performance analysis. Once the SQL performance analysis is complete, the process then obtains the total estimated I / O for the workload, weighted by the number of executions, and uses this as the score for the candidate method.

[0013] A method is provided to implement candidate partitioning schemes and verify the benefits of the selected scheme. The process implements the selected partitioning scheme as an implemented partitioned table populated with production data, and then runs the actual user workload against the partitioned table. Performance data for executing the workload is then collected. In one embodiment, to evaluate the performance, an optimistic approach can be taken - where it is assumed that the candidate will be accepted. In this case, modifications are made to the existing non-partitioned table to use the candidate partitioning scheme, and the above-described optimizer feature is enabled for all existing queries to avoid potential performance regressions. The method verifies the queries by running them against the partitioned table, allowing the optimizer to generate new plans. Once it is proven that there is no regression, the system allows the queries to be executed for the user with the new plans. After all queries are evaluated, the results are aggregated to quantify the benefit of the partitioning scheme for all queries in the workload. The performance data for the partitioned table is compared to the performance data for the original table (e.g., non-partitioned table), and it is determined whether the partitioned table provides sufficient benefit such that the partitioning scheme should be retained. If the performance of the partitioning scheme is deemed acceptable, then it is retained. Otherwise, the partitioning scheme is removed, and / or another scheme is selected from the set of candidate schemes as a replacement.

[0014] Another embodiment provides additional optimizations related to the autonomous implementation of interval / range partitioning. The problem addressed is that conventional methods for implementing interval partitioning may not work correctly when used for autonomous partitioning. Embodiments of the invention provide a method for performing automatic interval partitioning, wherein the inventive concept implements interval partitioning without imposing these implicit constraints on the partition key column. Automatic intervals are established where partition numbers are not directly linked to the data within the partition. Alternatively, the method uses an automatic list partitioning feature behind the scenes, which provides the ability to map individual values ​​to partition numbers as an indirect layer between the data in the partition and the partition numbers. A range wrapper layer overlays the interval partitioning imposed on the base table. Internally, the underlying data is actually implemented as list partitioning. A mapping layer is logically used to map range partitions to list partitions. A dummy column is created for each range partition table, where the dummy column logically appears identical to any other column in the table, but the values ​​in the dummy column are derived or generated by expressions rather than stored physically. The dummy column is used to store the key values ​​for the range-based partitions, representing the canonical values ​​of a given interval. Behind the scenes, the actual table structure used to hold the data is implemented as list-based partitioning. The mapping layer is used to map range-based partition keys to the underlying list-based partitions.

[0015] In various embodiments, any of the above combinations can be combined to perform in any variation, and many such combinations of aspects of the above elements are expected.

[0016] Further details regarding aspects, objects, and advantages of the invention are described below in the detailed description, accompanying drawings, and claims. The foregoing general description and the following detailed description are exemplary and illustrative, and are not intended to limit the scope of the invention. Attached Figure Description

[0017] The accompanying drawings illustrate the design and practicality of some embodiments of the invention. It should be noted that the drawings are not drawn to scale, and elements with similar structures or functions are indicated by similar reference numerals in all drawings. To better understand how the above and other advantages and objects of the various embodiments of the invention are obtained, the invention, which has been briefly described above, will be described in more detail with reference to specific embodiments illustrated in the accompanying drawings. It should be understood that these drawings depict only typical embodiments of the invention and are therefore not to be considered as limiting its scope. The invention will be described and explained with additional specificity and detail using the drawings, in which:

[0018] Figure 1 The illustration shows a system for implementing some embodiments of the present invention.

[0019] Figure 2 A flowchart illustrating a method for implementing some embodiments of the present invention is shown.

[0020] Figure 3 A flowchart of a method for generating multiple candidate partitioning schemes according to some embodiments of the present invention is shown.

[0021] Figure 4 Example information about a query on the table under consideration is shown, which provides useful information for generating scores for different candidates.

[0022] Figure 5 A flowchart illustrating the method for implementing the evaluation phase of candidate partitioning schemes is shown.

[0023] Figures 6A-6H Illustrative examples of some of the above processing steps are provided.

[0024] Figure 7 A flowchart illustrating a method for implementing candidate partitioning schemes and verifying the benefits of the selected scheme is shown.

[0025] Figure 8 Some embodiments of the invention are illustrated, wherein automatic intervals are established such that partition numbers are not directly linked to data within the partitions.

[0026] Figure 9 A flowchart illustrating the setup and implementation of automatic interval partitioning processes according to some embodiments of the present invention is shown.

[0027] Figure 10 This is a block diagram of an illustrative computing system applicable to implementing embodiments of the present invention.

[0028] Figure 11 This is a block diagram of one or more components of a system environment in which a service can be provided as a cloud service, according to an embodiment of the present invention. Detailed Implementation

[0029] Various embodiments are described below with reference to the figures. It should be noted that the figures are not necessarily drawn to scale. It should also be noted that the figures are for illustrative purposes only and are not intended as an exhaustive description of the invention or a limitation on its scope. Furthermore, the illustrated embodiments do not necessarily possess all the aspects or advantages shown. Aspects or advantages described in connection with a particular embodiment are not necessarily limited to that embodiment and may be practiced in any other embodiment, even if not illustrated. Moreover, references throughout this specification to “some embodiments” or “other embodiments” mean that a particular feature, structure, material, or characteristic described in connection with an embodiment is included in at least one embodiment. Therefore, the phrases “in some embodiments” or “in other embodiments” appearing throughout this specification do not necessarily refer to the same one or more embodiments.

[0030] As stated above, the problem addressed by this disclosure is that manual implementation of partitioning is often fraught with potential problems and errors, and is typically performed only by expert users. To address this problem, embodiments of the present invention provide a method for autonomously implementing partitioning in a data processing system without requiring any user-side expertise.

[0031] Figure 1 The illustration depicts a system for implementing some embodiments of the present invention to autonomously partition data within a data processing system. System 100 (which may be implemented as a database management server) includes a database 120 having one or more tables operated by one or more clients within the system. One or more users at the client can operate a user station to issue commands for processing of the tables by the database 120. The user station and / or server hosting the database includes any type of computing device that can be used to implement, operate, or interface with the database system 100. Examples of such devices include, for example, workstations, personal computers, mobile devices, servers, hosts, nodes, or remote computing terminals. The user station includes a display device, such as a display monitor, for displaying a user interface to the user at the user station. The user station also includes one or more input devices for the user to provide operational control over activities of the system 100, such as a mouse or keyboard for manipulating pointed objects in a graphical user interface to generate user input.

[0032] Interacting with a database involves submitting commands that instruct the database to perform operations on its data. For the database server to process these commands, they typically conform to a database language supported by the server. One example of a commonly used database language supported by many database servers is Structured Query Language (SQL).

[0033] When a database server (e.g., from a database application) receives the raw statement of a database command, it must first determine which actions should be taken in response and then execute those actions. The query processor processes the database statement to prepare the actions (arts) required to perform the desired actions within the database. The initial action of analyzing the database statement and breaking it down into its components can be called "parsing" the database command. The database command can then be "compiled" to create specific combinations and sequences of actions to be taken against one or more sets of database objects described, as well as code generation that may be needed to create an executable version of the database statement. Since multiple alternative processing paths can be used within the database to achieve the same result, "optimization" can be performed to identify specific processing paths that can be chosen to improve the processing efficiency of the database command. The actual execution of the actions applied to the database is typically referred to as "executing" the database command.

[0034] As shown in the diagram, a database may include one or more non-partitioned tables 110a. If non-partitioned table 110a can be converted into partitioned table 110b, there are likely many optimizations and advantages, as partitioning can provide significant benefits to a wide variety of applications by improving performance, manageability, and availability. It is not uncommon for partitioning to improve the performance of certain queries or maintenance operations by an order of magnitude. Furthermore, partitioning can greatly simplify common administrative tasks.

[0035] For example, partition pruning can be applied to significantly improve the performance of a database system. If a database query is issued against a table and the query contains search terms limited by a range or value corresponding to a partitioning criterion, then only partitions with the expected range or value are searched; all other partitions outside the expected range are pruned from the search. Partition pruning can typically improve query performance by several orders of magnitude. For example, suppose an application contains a table that holds order history and is partitioned by week. A query requesting orders for a single week will only access a single partition of that order table. If the order table has two years of historical data, then this query will access one partition instead of 104 partitions, which could potentially make the computational system execute 100 times faster due to partition pruning.

[0036] Partitioning can also improve the performance of multi-table joins by using a technique called partitioned join. Partitioned joins can be applied when two tables are joined together and both tables are partitioned on the join key, or when a partitioned table is referenced and joined with its parent table. Partitioned joins break down a large join into smaller joins that occur between each partition, thus completing the overall join in less time while also reducing space requirements. This provides significant performance benefits for both serial and parallel execution.

[0037] Partitioning also allows tables and indexes to be divided into smaller, more manageable units, giving database administrators the ability to manage data using a "divide and conquer" approach. With partitioning, maintenance operations can be focused on specific parts of a table. For example, a database administrator can back up a single partition of a table instead of the entire table. For maintenance operations across the entire database object, these operations can be performed on a per-partition basis, thus dividing the maintenance process into more manageable chunks. An example of using partitioning for manageability is supporting "rolling window" load processing in a data warehouse. For example, consider if a DBA loads new data into a table every week. The table can be partitioned so that each partition contains one week's worth of data. The load process simply uses partition swapping to add new partitions, where adding a single partition is far more efficient than modifying the entire table because the DBA doesn't need to modify any other partitions.

[0038] Partitioned database objects also provide partition independence. This feature of partition independence can be an important part of a high availability strategy. For example, if one partition of a partitioned table becomes unavailable, all other partitions of the table remain online and available. Applications can continue to execute queries and transactions on the available partitions of the table, and these database operations will run successfully, provided they do not need to access the unavailable partition.

[0039] In many database systems, database administrators can also specify that each partition be stored in a separate tablespace, with the tablespaces residing on different storage tiers. Storing different partitions in different tablespaces allows database administrators to perform backup and recovery operations on each individual partition, independent of other partitions in the table. This allows the active portions of the database to become available more quickly, so that system access can continue while inactive data is still being recovered. Furthermore, partitioning can reduce scheduled downtime. The performance gains provided by partitioning allow database administrators to perform maintenance operations on large database objects within relatively small batch windows.

[0040] In embodiments of the invention, the automatic partitioner 130 operates in system 100 to automatically partition the non-partitioned table 110a. Specifically, the automatic partitioner 130 receives certain information about the non-partitioned table 110a. For example, some embodiments collect information such as data about the table itself (e.g., table size, table schema information, and table statistics such as cardinality statistics), information about the workload applied to the table (e.g., SQL statements applied to the table as part of a user workload and interpretation of the execution plans used for the SQL statements), and information about performance metrics captured while running workloads against the non-partitioned table 110a.

[0041] Automatic partitioner 130 uses the collected information to generate multiple candidate partition schemes 140a-140n. Each of the multiple candidate partition schemes 140a-140n belongs to a different method of potentially partitioning non-partitioned table 110a into partitioned table 110b. Candidate partition schemes 140a-140n can cover different partitioning methods, including different types of partitioning and partitioning based on different columns within a given table (e.g., one or more partition keys).

[0042] Partition scheme selector 132 operates to select from multiple candidate partition schemes 140a-140n to choose one of the candidate schemes expected to provide the best system performance. Note that at this stage, candidate partition schemes 140a-140n are not yet implemented as fully populated tables with all production data from tables in the main database system. Instead, as will be described in more detail below, the candidate schemes at this stage are implemented as currently empty, real tables, where comprehensive statistics are used to predict and estimate the expected performance of each candidate scheme.

[0043] The selected partitioning scheme is then implemented within the database system, creating new partitioned tables and populating them with real production data. Actual user workloads are run against the populated partitioned tables to collect real-world execution results from the application of the selected partitioning scheme and compare these results to the performance of the previously unpartitioned database tables. The selected partitioning scheme can then be verified as successful, or exit if unsuccessful. In one embodiment, if successful, the partitions are applied to the user's table; if rejected, the "exit" process discards the new table.

[0044] The current method takes the workload and the name of the table to be partitioned as input. It assumes the system has accurate statistics for the partitioned tables and can query tables referenced within the workload.

[0045] Figure 2 A high-level flowchart illustrating a method for implementing some embodiments of the present invention is shown. As illustrated, the method is configured as a three-stage pipeline. At stage 202, the process generates multiple candidate partitioning schemes. As previously described, each candidate partitioning scheme can encompass different partitioning methods, including different types of partitions. The specific way of implementing partitioning is generally referred to as a "partitioning scheme." A partitioning scheme is described by (a) a partitioning method, (b) a partitioning key column, and (c) a set of partition boundaries. It is the combination of this method, keys, and boundaries that defines the partitioning scheme. Therefore, modifying at least one of these items creates a unique and independent partitioning scheme.

[0046] You can apply any number of different partitioning methods to partition database objects / tables, such as: (a) range partitioning, (b) hash partitioning, or (c) list partitioning. Each of these methods serves as a data distribution method that controls how data is placed into the partitions, where each row in the partitioned table is explicitly assigned to a single partition.

[0047] Range partitioning maps data to partitions based on the range of values ​​for the partition key that is created for each partition. This is a very common partitioning type and is often used with dates. For example, for a table with a date column as the partition key, where the partition boundaries are configured to correspond to different monthly partitions, the "January 2018" partition would contain rows with partition key values ​​from January 1, 2018 to January 31, 2018.

[0048] Hash partitioning is used to map data to partitions based on a hash algorithm applied to the partition key. If designed correctly, the hash algorithm distributes rows evenly across partitions (e.g., hash buckets), resulting in partitions of roughly the same size. Therefore, this type of partitioning is a good way to distribute data evenly across storage / processing devices.

[0049] List partitioning allows explicit control over how rows are mapped to partitions by specifying a list of discrete values ​​for the partition key in the description of each partition. The advantage of list partitioning is that this method can group and organize unordered and unrelated datasets.

[0050] A partition key is referenced by each partitioning method and consists of one or more columns that determine the partition where each row will be stored. The database automatically performs insert, update, and delete operations on the appropriate partitions using the partition key.

[0051] Partition boundaries specify values ​​for how rows map to partition keys corresponding to different partitions. For example, for list partitioning, the boundary can be one or more specific values ​​associated with a given partition. As another example, regarding range partitioning, each partition can have a "VALUES LESS THAN" clause, which specifies a non-inclusive upper bound for the partition. Any partition key value equal to or higher than this literal will be added to the higher partition. There may be multiple higher partitions, and values ​​higher than the PART1 boundary can go into PART2, PART3, or PARTn. All partitions except the first have an implicit lower bound specified by the "VALUES LESS THAN" clause of the previous partition. A "MAXVALUE" data item can be defined for the highest partition, representing a virtual infinite value ordered above any other possible value of the partition key.

[0052] At stage 204, this process evaluates different candidate partitioning schemes. As previously mentioned, the candidate schemes at this stage are implemented as a currently empty reality table, where aggregated statistics are used to predict and estimate the expected performance of each candidate scheme. Therefore, the actual workload can be compiled and used in combination with the aggregated statistics to estimate the performance of each candidate partitioning scheme.

[0053] For example, one approach is to use comprehensive statistics to analyze the compiled workload against the candidate partition table to estimate the amount of I / O expected to be performed for each candidate partition scheme when operated by customer workloads, and to identify the candidate partition scheme that is expected to generate the lowest total I / O.

[0054] At stage 206, the process implements the selected partitioning scheme within the database. An actual workload is executed to quantify the workload's actual performance against real data already populated within the implemented table partitions. In this current embodiment, the measured performance is used to check, for example, one or more of the following: (a) verifying and quantifying any benefits derived from the partitioning; and / or (b) verifying that there are no query rollbacks in the workload.

[0055] The first and second phases are used to identify proposed partitioning schemes, and in some embodiments, they can run without a third phase to obtain recommendations without actually implementing them. The recommendation phase can run periodically, each time using the latest workload and table statistics. Then, when recommendations converge (e.g., a candidate is considered good N times consecutively), the processor / user can then choose to continue to the third phase. After accepting a candidate scheme, the recommendation phase can continue to run periodically to check whether the selected scheme remains a good choice as the workload evolves.

[0056] Note that any of the above processing actions can be implemented in either a production or testing environment. Only the final stages of a partitioning scheme that is deemed beneficial and acceptable in practice need to be implemented in a production environment (as a replacement for existing tables in the production environment).

[0057] Figure 3 A flowchart illustrating a method for generating multiple candidate partitioning schemes according to some embodiments of the present invention is shown. At 302, a table within the database is identified for possible partitioning. Any suitable method can be used at this stage to identify the table for evaluation. For example, one approach is to exclude any table that has already been partitioned by the user in a specific manner. Another approach is to select the currently unpartitioned table with the largest size (e.g., based on row count / data volume).

[0058] In some embodiments, the cost of implementing partitioning (e.g., the cost of moving data within the system to achieve a given partitioning scheme) can be balanced against the potential benefits of implementing the new partitioning scheme in order to select tables for automatic partitioning. The return on investment for any costs of performing partitioning can likely be recovered more quickly through efficiency gains from more frequently accessed tables, while infrequently accessed tables may not be worth the effort and resources spent partitioning. Therefore, some embodiments may consider historical access patterns of tables to anticipate future intended use and identify specific tables that may provide the greatest efficiency gain for partition selection.

[0059] Any suitable method can be used to initiate partitioning to identify tables for automatic partitioning. One possible approach is to have users manually identify the tables to be partitioned. Another approach is to automatically identify and trigger partitioning. For example, for tables that are not currently partitioned, this can be triggered by identifying that the table has reached a specified threshold size, receives a threshold number of queries against the table in the workload, or any other factors or combinations thereof that indicate a table might potentially benefit from partitioning. For tables that are already partitioned, this can be triggered, for example, if the number of partitions reaches a threshold number considered excessive, if the size of a single partition reaches a threshold size, and / or any other factors or combinations thereof that indicate a table might potentially benefit from repartitioning. Furthermore, the database schema can be configured to establish blacklist and / or whitelist parameters to specify specific tables that automatic partitioning should or should not resolve.

[0060] Next, queries within the user workload that actually reference the selected table can be identified. For example, in Oracle database products, a database object called a "SQL Tuning Set" or "STS" can be used to identify the set of SQL statements belonging to a given database table. The STS can include various pieces of information used by the automatic partitioner, such as (a) the set of SQL statements, (b) any associated execution context, such as user schema, application module(s) name and(s) action(s), bind value list, and the SQL compilation environment of the associated cursor, (c) associated basic execution statistics, such as elapsed time, CPU time, buffer fetch, disk read, rows processed, cursor fetch, execution count, full execution count, optimizer cost, and command type, and / or (d) the associated execution plan and row source statistics for each SQL statement. Queries that do not belong to the selected table are not considered at this stage.

[0061] For each query belonging to the selected tables, at step 306, the execution plan for that query is obtained and examined. For example, within database products provided by Oracle Corporation, the "EXPLAIN PLAN" method can be used to identify and examine the execution plan. This command identifies the execution plan selected by the database optimizer to implement the SQL statement. The execution plan of a statement is the sequence of operations performed by the database to run that statement. The row source tree is the core of the execution plan. It displays the following information: (a) the order in which the statement references the tables, (b) the access method for each table mentioned in the statement, (c) the join method for tables affected by join operations in the statement, and / or (d) data operations such as filtering, sorting, or aggregation. In addition to the row source tree, the plan table may contain information about one or more of the following: (a) optimizations, such as the cost and cardinality of each operation, (b) partitioning, such as the set of partitions accessed, and / or (c) parallel execution, such as the distribution method of join inputs. In some embodiments, simply parsing the output of the actions may provide sufficient information without going through the entire Explain Plan process.

[0062] Columns frequently used in predicates are good candidates for partition keys. This is because columns used in highly selective predicates are often the best candidates, as they provide the best performance improvement through proper partitioning. However, engineering work is often required to achieve predicate selectivity, especially for join predicates.

[0063] Therefore, some embodiments of the present invention identify and generate predicate counts within SQL statements, and then use the predicate counts to find candidate partition keys. The EXPLAIN PLAN function can be used to provide predicate information for each statement, interpreting each query in the workload once, and then using the query execution count to calculate the number of table scans performed for each predicate. At this point, predicate data 320 has been generated, which identifies the set of all predicates for each relevant SQL statement.

[0064] Note that the cardinality data 322 of database objects within the database can also be used in embodiments of the present invention. Cardinality refers to the number of distinct values ​​in a given column. Cardinality information is useful for determining which type of partitioning should be applied to a particular column. In some embodiments, the cardinality data 322 used in the present invention can be obtained from database statistics that have been maintained and used by the query optimizer of the database system.

[0065] At point 312, a set of rules can be used to perform an initial filtering step to remove suboptimal candidates. For example, generally speaking, if the cardinality of a column is too low (e.g., below a specified threshold), then hash partitioning will not be optimal and may be filtered out. This is because this approach does not provide a good distribution across hash buckets. On the other hand, if the number of unique values ​​is too high (e.g., above a specified threshold), then list partitioning will not be the best way to achieve partitioning because it will create too many partitions relative to the amount of data.

[0066] At point 314, a set of candidate partitioning schemes can now be identified based at least on predicate and cardinality information. For each column, the methods in some embodiments consider several possible partitioning methods, including interval / range partitioning, hash partitioning, and list partitioning. Candidate partitioning schemes are generated based on at least two factors, including (a) the set of predicates in the query and (b) the number of distinct values ​​within a given column. For example, for equality predicates, any partitioning method can be appropriately used, provided cardinality data is carefully considered. As another example, for range predicates, range partitioning or list partitioning can be used, but hash partitioning may not be optimal.

[0067] If the partition key is a type that fits within a range of values, and especially if the predicate is a range-based predicate against that partition key, then range partitioning (also known as range-based partitioning) should be considered. As an example, partition keys such as DATE, TIMESTAMP, or NUMBER data types are typically suitable for range partitioning. Range partitioning requires the system to specify a boundary for the first partition, which acts as an anchor point for calculating the size of the other partitions and the range. In some embodiments, for DATE and TIMESTAMP data types, the method queries the table to obtain the first percentile value of the partition key (thus discarding outliers), for example, where the range size is always 1 month, and the first partition boundary is rounded to the first percentile of the 1st of the month. For the NUMBER data type, the method queries the table to obtain the 1st and 99th percentile values ​​of the partition key (thus discarding outliers); the range size is (99th percentile - 1st percentile) / N, rounded to two significant digits. Here, N is a parameter that controls the number of partitions in the table. In one embodiment, N is calculated at least in part based on the size of the table. For example, N can be configured where N = (table size / 10GB); if N < 8, then N = 8; if N > 1024, then N = 1024. The first partition boundary is the first percentile, rounded to a multiple of the interval.

[0068] It should be noted that some methods for implementing range partitioning may have limitations that prevent their autonomous use in cloud environments where data is unpredictable. Specifically, in some implementations, the range partitioning key cannot be NULL and may have an implicit upper bound. See below for details.Figure 8 and Figure 9 In more detail, some embodiments of the present invention use virtual columns and Listautomatic partitioning to implement range partitioning to address these problems.

[0069] In some embodiments, hash partitioning is considered if a column has a number of distinct values ​​greater than or equal to a certain number (e.g., 64 distinct values) and an equality predicate exists on the column. In one embodiment, hash partitioning is considered as an option if the number of unique values ​​is greater than 32. However, in some embodiments, hash partitioning is not considered if only range predicates are identified for the column.

[0070] In some embodiments, list partitioning is considered if a column has multiple distinct values ​​within an acceptable range (e.g., at least 4 and at most 128 distinct values). This range provides a reasonable distribution of data across a defined set of partitions. In some embodiments, list partitioning is considered as an option if the number of unique values ​​is less than 64.

[0071] At step 316, a score is identified for each different candidate partitioning scheme. At step 318, the candidates are ranked based on their individual scores, and the candidate list is pruned so that only a subset of the candidates proceeds to the next stage for more detailed analysis and consideration.

[0072] One approach to scoring is to examine how many types of predicates are associated with a given column within a table and use that information to score any associated partitioning schemes based on those predicate types for the specified column. This type of calculation can indicate the level of efficiency gained by implementing certain types of partitioning for a column. For example, if the first column has only a single predicate, but the second column has one hundred predicates, then partitioning might produce more efficient results for the second column compared to the first column.

[0073] Figure 4 Example table 402 shows information about the queries on the tables under consideration, providing useful information for generating scores for different candidates. Each row identifies different predicates within the SQL statement that belong to columns within the table. Here, the first four rows of the table identify various SQL statements (SQL 1-4) and predicates belonging to column A, the fifth row identifies SQL statements with predicates belonging to column B (SQL 5), and the sixth row identifies SQL statements with predicates belonging to column C (SQL 6).

[0074] Suppose the first candidate partitioning scheme has a partition key corresponding to column A and uses the partition type corresponding to the predicates in the first four rows (SQL 1-4). In this case, a score of "4" can be given to the candidate to identify the fact that four predicates are associated with this candidate partitioning scheme. Suppose the second candidate partitioning scheme has a partition key corresponding to column B and uses the partition type corresponding to the predicate in the fifth row (SQL 5). In this case, a score of "1" can be given to the second candidate to reflect the fact that a single predicate is associated with this candidate partitioning scheme. Similarly, suppose the third candidate partitioning scheme has a partition key corresponding to column C and uses the partition type corresponding to the predicate in the sixth row (SQL 6). In this case, a score of "1" can also be given to the third candidate to reflect the fact that a single predicate is associated with this candidate partitioning scheme. In this case, the candidates can be ranked based on their scores, and the first candidate associated with column A will receive the highest score because it is associated with the most predicates. Note that in some embodiments, the above predicate counts can be weighted by the SQL execution count.

[0075] Based on their respective scores, the top n partitioning schemes are evaluated during the evaluation phase. Figure 5 A flowchart illustrating a method for implementing the evaluation phase for candidate partitioning schemes is shown. For each candidate partitioning scheme, the process at step 506 creates an empty partitioned table with columns, indexes, and constraints cloned from the original table. Since an empty table is created, this means no data segments are allocated for the empty table. The method materializes partitions for ranges and automatically lists candidates to match the data in the original table. For example, this can be achieved by using the DDL "create table for exchange" to create an empty partitioned table as a clone of the original table. This DDL is responsible for cloning the table with exact column attributes and copying constraints. Furthermore, in some embodiments, the DDL also clones the indexes on the original table. This creates an empty partitioned table with exact copies of the columns, constraints, and indexes.

[0076] Next, at step 508, statistics are synthesized for the empty partitioned table, its partitions, its columns, and its indexes. Since this process works with empty partitioned tables, this means that even though these are real tables that have been created and exist in the database data dictionary, all the actual data from the original non-partitioned tables is not copied to these tables at this stage—therefore, references to these tables are "empty." If no real data is available, then the database engine (e.g., the database optimizer) will need some dataset that it can act upon to evaluate these empty tables. For the current embodiment, this means that statistics are "synthesized" to provide that data for the optimizer to act upon.

[0077] In some embodiments, aggregated statistics are provided for empty partitioned tables, where table statistics are copied from the original table—but constructed within the context of the partition structure of the respective candidate partitioning scheme. This can be done, for example, when using database products available from Oracle Corporation, by using commands such as DBMS_STATS.GET_TABLE_STATS and DBMS_STATS.SET_TABLE_STATS.

[0078] Partition statistics, such as those used when using database products available from Oracle, are constructed as part of the original table statistics using the DBMS_STATS.GET_TABLE_STATS and DBMS_STATS.SET_TABLE_STATS commands. During the operation, a query is run to identify the number of rows in each partition and the total number of rows in the table. The statistics are then scaled by a factor of "number_of_rows_in_partition / number_of_rows_in_table". Scaling can be performed on many attributes, such as the number of rows (numrows), the number of blocks (numblks), and / or the cached blocks (cachedblk) attribute.

[0079] You can use DBMS_STATS.GET_COLUMN_STATS and DBMS_STATS.SET_COLUMN_STATS to copy column statistics from the original table. You can use DBMS_STATS.GET_INDEX_STATS and DBMS_STATS.SET_INDEX_STATS to copy index statistics from the original index.

[0080] Note that in some implementations, it is not necessary to adjust the statistics for changes in rowid length. For example, an index on a non-partitioned table will have a 6-byte rowid, and a global index on a partitioned table will have a 10-byte rowid, but the current method will use the same statistics for both indexes.

[0081] Additional filtering actions can occur to remove any candidates from consideration that have suboptimal results identifiable from the statistical synthesis process. For example, after synthesizing statistics, the process can optionally be configured to reject candidates that might result in more than one-third of the rows entering a partition. This is to avoid performance issues due to partition skew and maintainability issues that might arise due to large segment sizes.

[0082] At this point, the user workload can be compiled and the optimizer can estimate the workload's performance for partitioned tables with comprehensive statistics (510). One problem that needs to be addressed is that SQL statements in the workload may be referencing the original table objects instead of newly created table objects corresponding to the candidate partitioning scheme. One way to solve this problem is to provide a mechanism that automatically converts one table name to another during SQL compilation. This mechanism is set up to convert the original table names to the names of empty partitioned tables. Then, SQL performance analysis is used to compile each query in the workload. I / O cost calculations can be generated during SQL performance analysis (512).

[0083] Once SQL performance analysis is complete, the process obtains a total estimated I / O for the workload, weighted by execution count, and uses it as a score for candidate methods. In some embodiments, the standard IO_COST metric is not used to evaluate candidate partitioning schemes because they may not account for certain partition pruning methods, such as subquery pruning or Bloom pruning. Therefore, some embodiments may employ a custom IO_COST metric for table scans on empty partitioned tables, where IO_COST is calculated as a metric stored in the plan. The metric is calculated by finding all partitions in the table that will be accessed, where the process examines the bindings to find the set of partitions to be accessed. Then, using partition-level statistics, the process sums the number of blocks in these partitions, and this sum is the IO_COST metric.

[0084] Regarding Bloom pruning, consider cases where there is a join between a partitioned fact table and a non-partitioned dimension table, where the join key is the partition key of the fact table. For example, consider the following query:

[0085] SELECT...FROM FACT F, DIM D

[0086] WHERE F.PARTITION_KEY=D.KEY

[0087] and D. ATTRIBUTE < 1000;

[0088] This type of query typically uses Bloom pruning to restrict the scan of the fact table to partitions that match the join keys found when scanning the dimension tables. Traditionally, Bloom pruning is performed only when the query is executed, and therefore is not considered in the optimizer cost model at all.

[0089] Therefore, when attempting to estimate the performance of an empty partitioned table with comprehensive statistical information using an optimizer, a conventional optimizer may fail to consider Bloom pruning. For example, in a data warehouse environment, many queries typically use Bloom pruning. Therefore, current processing should consider this pruning when calculating estimated I / O for candidate partitioning schemes.

[0090] To address this issue, the current embodiment implements a mechanism that runs a recursive query on the dimension table to find the resulting join key. Given the join key, processing can find the partition of the fact table containing the join key. Continuing the example above, the following can be generated:

[0091] SELECT DISTINCT TBL$OR$IDX$PART$NUM(FACT,0,0,0,D.KEY)

[0092] FROM DIM D WHERE D.ATTRIBUTE<1000;

[0093] This query returns the set of partitions that Bloom trimming would return. This query can be generated even if there are no filter predicates on the dimension tables.

[0094] After processing all candidates, the processing at position 516 will sort the candidates in descending order of estimated I / O and use the highest result as the recommended method for partitioning.

[0095] Figures 6A-6H Illustrative examples of some of the above processing steps are provided. Figure 6A The example table 602, which is currently unpartitioned, is shown. Table 602 has a very simple structure with a single column (“Salary” column) that currently has ten rows.

[0096] Figure 6B Example candidate partitioning schemes 604 that have been identified for Table 602 are shown. In particular, candidate partitioning scheme 604 describes a range partitioning scheme with an interval “2” and a first upper band identified as “2”.

[0097] If this partitioning scheme is actually applied to Table 602, the result will be as follows: Figure 6C The partition shown in -G. Specifically, as... Figure 6C As shown, partition P1 is defined as including all rows in the Salary column where the value is less than the value "2". Therefore, rows 1, 4, and 9 in Table 602 will be used to populate partition P1. Figure 6D As shown, partition P2 is defined as all rows whose values ​​in the Salary column are less than the value "4", therefore rows 5 and 7 in Table 602 will be used to populate partition P2. Figure 6E As shown, partition P3 is defined as including all rows where the value in the Salary column is less than the value "6", therefore the second and tenth rows in Table 602 will be used to populate partition P3. Figure 6FAs shown, partition P4 is defined as including all rows where the value in the Salary column is less than the value "8", therefore the third and eighth rows in Table 602 will be used to populate partition P4. Finally, as Figure 6G As shown, partition P5 is defined as including all rows in the Salary column whose values ​​are less than the value "10", so only the sixth row in Table 602 will be used to populate partition P5.

[0098] For the purposes of this invention, empty partitions will remain "empty" and will not actually be filled with real data. Alternatively, as Figure 6H As shown, the data from the original table 602 will be logically analyzed as if the partitions were actually populated as shown in 608, but will actually only be used for a comprehensive set of statistics, such as statistics 610 shown in this figure. If the partitions were actually populated with the original data, then these partition-level statistics 610 will identify the number of rows from the original table 602 that will be placed in each partition on a partition-by-partition basis. For example, the third column in table 610 identifies the number of rows in each partition in the candidate partitioning scheme. Based on the comprehensive statistics, one or more performance metrics can be established for the candidate partitioning scheme. For example, as shown in the fourth column of statistics 610, the number of blocks corresponding to each partition can be estimated by calculating the relative percentage of rows in a given partition and multiplying it by the size (S) of the database object. In this way, using partition-level statistics, the number of blocks across partitions of the candidate partitioning scheme can be summed, and this sum can be used to provide an IO_COST metric for evaluating the partitioning scheme. In some embodiments, the process of obtaining the IO_COST summation spans the partitions that will be scanned by each query, where not all partitions will be scanned due to partition pruning. Based on the IO_COST metric for each scheme, the scheme with the best estimated performance in terms of I / O can be identified as the specific scheme to be implemented in the next stage of processing.

[0099] Figure 7 A flowchart illustrating the method for implementing candidate partitioning schemes and verifying the benefits of the selected scheme is shown. At 702, the process implements the selected partitioning scheme as an implemented partition table populated with production data. At 704, a real user workload is then run against the partition table. Performance data from the executed workload is then collected at 706.

[0100] In this current embodiment, an optimistic approach can be taken to evaluate performance—assuming the candidate will be accepted. In this case, the existing non-partitioned table is modified to use the candidate partitioning scheme, and the aforementioned optimizer features are enabled for all existing queries to avoid potential performance fallback. This method validates the queries by running them against the partitioned table, allowing the optimizer to generate a new plan. Once it is proven that there is no fallback, the system allows the queries to be executed for the user with the new plan. After evaluating all queries, the results are aggregated to quantify the benefit of the partitioning scheme to all queries in the workload.

[0101] At step 708, the performance data of the partitioned table is then compared with the performance data of the original table (e.g., a non-partitioned table). At 710, it is determined whether the partitioned table provides sufficient benefit for the partitioning scheme to be retained. In some embodiments, the performance improvement should increase by at least 20% to be considered sufficient to retain the proposed partitioning scheme. If the performance of the partitioning scheme is deemed acceptable, it is retained at step 712. Otherwise, at 714, the partitioning scheme is removed, and / or another scheme is selected from the candidate scheme set as an alternative.

[0102] This document will now describe additional optimizations related to the autonomous implementation of range / range partitioning. The problem addressed is that conventional methods for implementing range partitioning may not function correctly when used for autonomous partitioning. As previously mentioned, range partitioning is a method of creating partitions in a range-partitioned table when data is inserted. In some implementations, this method includes two implicit constraints on the partition key of the range-partitioned table: (i) the partition key column cannot be null; and (ii) the partition key column has an implicit upper limit. In other words, some conventional methods for implementing range partitioning cannot allow the partition key to be null and cannot handle values ​​exceeding this upper limit. These implicit constraints are generally not a problem for application developers who can anticipate the data in the table. However, in autonomous environments where data is unpredictable and can change arbitrarily over time, these constraints can prevent the system from using range partitioning on user tables.

[0103] Note that partitioning layers can internally use partition numbers to identify partitions. For example, partition pruning works by finding the set of partitions relevant to the query and returning these partition numbers to the row source that performed the scan.

[0104] The limitations of regular intervals arise from the implementation of linking partition numbers to values ​​within a partition. Conceptually, if value X goes into partition number Y, then value X*10 goes into partition number Y*10, even if there is no data between X and X*10. However, partition numbers are a finite resource; they can be limited. Therefore, the decision to assign partition numbers based on the data within a partition has an implicit constraint that can only support the maximum value of the partition key. Since NULL sorts high, this also means that NULL cannot be inserted for the partition key.

[0105] Embodiments of the present invention provide a method for performing automatic interval partitioning that solves these problems, wherein the inventive concept achieves interval partitioning without imposing these implicit constraints on the partition key column.

[0106] like Figure 8 As shown, in some embodiments of the invention, automatic partitions are established where partition numbers are not directly linked to data within a partition. Alternatively, the method uses an automatic list partitioning feature 806 behind the scenes, which provides the ability to map individual values ​​to partition numbers as an indirect layer between the data within a partition and the partition number.

[0107] Specifically, the range wrapper layer 804 overlays the range partitions imposed on the base table 802. Internally, the underlying data is actually implemented as list partitions 806. The mapping layer is logically used to map range partitions to list partitions.

[0108] Figure 9 A flowchart illustrating the setup and implementation of automatic range partitioning according to some embodiments of the present invention is shown. At 902, the partition key column is identified for the expected range partitioning. A range-based partitioning scheme is configured for the database table.

[0109] As part of the schema configuration for range-based partitioning, a "virtual column" is created for each range-partitioned table at step 904. A virtual column logically appears identical to any other column in the table, but the values ​​in the virtual column are derived or generated by expressions, rather than being physically stored. The virtual column is used to store the key values ​​for the range-based partitions, representing the canonical values ​​for a given range.

[0110] However, behind the scenes, at step 906, the actual table structure used to hold the data is implemented as list-based partitioning. At 908, a mapping layer is used to map the range-based partition keys to the underlying list-based partitioning.

[0111] As described above, a dummy column is added to the original user's table, and an empty partitioned table is created using the evaluated automatic range scheme. This means that the current approach may potentially affect the user's original table / application in some way before the partition scheme is evaluated as useful or not. To address this issue, some embodiments provide a method that allows the system to partition directly using expressions.

[0112] To illustrate, one possible approach to creating a table for scoring / evaluating partitioning schemes is as follows:

[0113] Create table y(a int);

[0114] insert into y values(1);

[0115] insert into y values(100);

[0116] insert into y values(123);

[0117] commit;

[0118] alter table y add(vc invisible as(sys_op_interval_high_bound(a,100,0)));

[0119] create table yexch partition by list(vc)automatic(partition p_nullvalues(null))for exchange with table y;

[0120] Using the current method of partitioning by expression, this example can now be implemented as follows:

[0121] Create table y(a int);

[0122] insert into y values(1);

[0123] insert into y values(100);

[0124] insert into y values(123);

[0125] commit;

[0126] create table yexch partition by list(sys_op_interval_high_bound(a,100,0))automatic(partition p_null values(null))for exchange with table y;

[0127] Using this method, tables can now be created without affecting the original user's tables in any way, because the virtual columns are automatically created as part of the table creation DDL. The same idea can be used when actually implementing the partitioning scheme for the original tables after validating the partitioning scheme for the user's workload:

[0128] alter table y modify partition by list(sys_op_interval_high_bound(a,100,0))automatic(partition p_null values(null))

[0129] This change table DDL will automatically create virtual columns internally. No additional steps are required to add VC and then partition the table beforehand.

[0130] Furthermore, when a partition key is mapped to a range, a canonical value is selected for the range, and this canonical value is used in conjunction with automatic list partitioning to locate the partition. To maintain consistency with range partitioning, this method selects the upper bound of the range as its canonical value. For example, if the range size is 100, then the value 123 will map to the range [100, 200), and the canonical value for this range will be 200. Conceptually, this is similar to automatic list partitioning using virtual columns.

[0131] The following is a syntax example with initial partitions and no fixed-point definitions:

[0132]

[0133] Using list partitioning does not require partitions to be sorted by their values, and the method of this invention does not require partition number gaps between partitions that have gaps in their values. Therefore, when creating partitions for N intervals, the method will use partition numbers 1..N, even if one interval is #1 and another interval is #1,000,000,000.

[0134] Note that HIGH_VALUE in the catalog view will be the same for both regular and automatic range tables. Furthermore, this method represents the range used for canonical calculations; it does not require or define whether the range begins with 0, 1, or any other arbitrary value.

[0135] Some embodiments belong to automatic intervals without a range portion. Because this method is not limited by partition numbers, automatic intervals can be supported without any range partitioning or any user-specified anchor points for interval calculations. However, fixed points can be used to correctly calculate interval boundaries, similar to transition points in an interval partitioning table. For numeric date types, intervals can be calculated starting from any number, such as 0. For date / time data types, intervals can be calculated starting from any date, such as January 1, 2000. Note that automatic intervals have no problem processing values ​​before anchor points; the value -1234 will map to an interval [-1300, -1200) with a canonical value of -1200, and this interval is just as valid as any other interval.

[0136] Some implementations can use methods such as the following syntax to create an automatic range partitioning table without range partitions, where the following is a syntax example without initial partitions and without fixed-point definitions:

[0137] CREATE TABLE AUTO_INTERVAL(i int,j int)

[0138] PARTITION BY RANGE(i)INTERVAL(100)AUTOMATIC;

[0139] Unlike earlier syntax, this does not represent a single partition at the time the initial table is created. This syntax will create one partition for NULL values, regardless of whether the partition key is nullable. There is no transition point or distinction between range partitioning and interval partitioning—all partitions in the table are interval partitions.

[0140] After discarding the final range partitions of a regular range partitioned table, the table can be implicitly converted into an automatic range partitioned table.

[0141] Compared to regular intervals, the current implementation of automatic intervals has many advantages, including: (1) the partition key has no implicit NOT NULL constraint; (2) the partition key has no implicit upper limit; (3) interval partitions with no range partitions, then DROP PARTITION does not require partition renumbering; and (4) partitions can be created at any point in time (ADD PARTITION).

[0142] In terms of implementation methods, some embodiments include partitioning logic for mapping partition keys to interval numbers and then using a hash table to obtain the partition number of that interval number. The system can handle range predicates within the interval portion. For this purpose, a method similar to that used for list partitioning can be adopted, with the following data structures: (a) a sorted array of partition boundaries in the interval portion, and / or (b) a parallel array of partition numbers.

[0143] Searching in these data structures is different from list partitioning search because the interval size is relevant. For example, if the predicate is col < X and the interval size is I, then the partition with the upper bound < (X + I) is returned.

[0144] List partitioning can produce a discontinuous set of partitions for range predicates, and this same approach can be used for range predicates in an auto - interval table. When materializing a new partition, the system can use the auto - list method logic where partitions are identified by key values rather than the interval method where partitions are identified by partition numbers.

[0145] Some embodiments provide a way to implement a definition of the fixed point of interval computations - either explicitly or implicitly. The fixed point of interval computations can be set implicitly or made user - controllable. Assuming that the functionality will be only autonomous, then an implicit definition can be implemented and / or enforced. In some implementations, the user should not be required to make data placement decisions (they can do so through explicitly - controlled partitioning as they would in a non - managed environment). The implicitly - declared fixed point can be: (a) the numerical value 0, and / or (b) the first day of the calendar year for a date / time - stamp column.

[0146] Thus, what is described is an improved method to perform automatic partitioning without any expertise on the user's part. A three - stage processing pipeline is provided to generate candidate partitioning schemes, evaluate the candidates using an empty real - table structure, and then implement the selected scheme using production data for evaluation.

[0147] In addition, an improved method for performing automatic interval partitioning has been described, where the inventive concept implements interval partitioning without imposing these implicit constraints on the partitioning key columns. The method can automatically capture the user's workload at its runtime. In addition, when future data cannot be predicted, the new partitioning method can be used to perform interval partitioning on a table. Recommendations can be validated by executing the workload against the partitioned table before applying them in the user's environment.

[0148] System Architecture

[0149] Figure 10 is a block diagram of an illustrative computing system 1500 suitable for implementing embodiments of the present invention. Computer system 1500 includes a bus 1506 or other communication mechanism for conveying information that interconnects subsystems and devices such as a processor 1507, system memory 1508 (e.g., RAM), static storage device (e.g., ROM), disk drive 1510 (e.g., magnetic or optical), communication interface 1514 (e.g., modem or Ethernet card), display 1511 (e.g., CRT or LCD), input device 1512 (e.g., keyboard), and cursor control.

[0150] According to some embodiments of the present invention, computer system 1500 performs a specific operation by executing one or more sequences of one or more instructions contained in system memory 1508 via processor 1507. Such instructions may be read into system memory 1508 from another computer-readable / usable medium, such as static storage device 1509 or disk drive 1510. In alternative embodiments, hardwired circuitry may be used in place of or in combination with software instructions to implement the invention. Therefore, embodiments of the invention are not limited to any particular combination of hardware circuitry and / or software. In some embodiments, the term "logic" should refer to any combination of software or hardware used to implement all or part of the invention.

[0151] As used herein, the terms "computer-readable medium" or "computer-usable medium" refer to any medium that participates in providing instructions to processor 1507 for execution. Such media can take many forms, including but not limited to non-volatile media and volatile media. Non-volatile media include, for example, optical discs or magnetic disks, such as disk drives 1510. Volatile media include dynamic memory, such as system memory 1508.

[0152] Common forms of computer-readable media include, for example, floppy disks, flexible disks, hard disks, magnetic tapes, any other magnetic media, CD-ROMs, any other optical media, punched cards, paper tapes, any other physical media with a perforated pattern, RAM, PROMs, EPROMs, FLASH-EPROMs, any other memory chips or cassette tapes, or any other media that a computer can read.

[0153] In embodiments of the invention, the execution of the instruction sequence for practicing the invention is performed by a single computer system 1500. According to other embodiments of the invention, two or more computer systems 1500 coupled via a communication link 1510 (e.g., a LAN, PTSN, or wireless network) can coordinate with each other to execute the instruction sequence required for practicing the invention.

[0154] Computer system 1500 can transmit and receive messages, data, and instructions, including programs, i.e., application code, via communication link 1515 and communication interface 1514. Received program code can be executed by processor 1507 upon receipt and / or stored in disk drive 1510 or other non-volatile memory for later execution. Database 1532 in storage medium 1531 can be used to store data accessible to system 1500.

[0155] The described technologies can be implemented using various processing systems, such as cluster computing systems, distributed systems, and cloud computing systems. In some embodiments, some or all of the above-described data processing systems may be part of a cloud computing system. A cloud computing system can implement cloud computing services, including cloud communication, cloud storage, and cloud processing.

[0156] Figure 11 This is a simplified block diagram of one or more components of a system environment 1600 according to an embodiment of the present disclosure, through which services provided by one or more components of the embodiment system can be provided as cloud services. In the illustrated embodiment, system environment 1600 includes one or more client computing devices 1604, 1606, and 1608 that can be used by a user to interact with a cloud infrastructure system 1602 providing cloud services. The client computing devices can be configured to operate client applications, such as web browsers, proprietary client applications, or some other applications, which can be used by the user of the client computing devices to interact with the cloud infrastructure system 1602 to use the services provided by the cloud infrastructure system 1602.

[0157] It should be recognized that the cloud infrastructure system 1602 depicted in the figures may have other components besides those depicted. Furthermore, the embodiment shown in the figures is merely one example of a cloud infrastructure system that can be incorporated into embodiments of the present invention. In some other embodiments, the cloud infrastructure system 1602 may have more or fewer components than shown in the figures, may combine two or more components, or may have different component configurations or arrangements.

[0158] Client computing devices 1604, 1606, and 1608 can be similar to those described above. Figure 10 The devices described. Although system environment 1600 is shown as having three client computing devices, it can support any number of client computing devices. Other devices, such as devices with sensors, can interact with cloud infrastructure system 1602.

[0159] One or more networks 1610 can facilitate communication and data exchange between clients 1604, 1606, and 1608 and cloud infrastructure system 1602. Each network can be any type of network familiar to those skilled in the art that can support data communication using any of a variety of commercially available protocols. Cloud infrastructure system 1602 may include one or more computers and / or servers.

[0160] In some embodiments, services provided by a cloud infrastructure system may include a variety of services available on demand to users of the cloud infrastructure system, such as online data storage and backup solutions, web-based email services, hosted office suites and document collaboration services, database processing, managed technical support services, etc. Services provided by a cloud infrastructure system can be dynamically scaled to meet the needs of users of the cloud infrastructure system. A specific instantiation of a service provided by a cloud infrastructure system is referred to herein as a "service instance." Generally, any service available to users from a cloud service provider's system via a communication network (such as the Internet) is referred to as a "cloud service." Typically, in a public cloud environment, the servers and systems that constitute the cloud service provider's system differ from the customer's own on-premises servers and systems. For example, a cloud service provider's system may host applications, and users may subscribe to and use these applications on demand via a communication network such as the Internet.

[0161] In some examples, services within a computer network cloud infrastructure may include protected computer network access to storage devices, hosted databases, hosted web servers, software applications, or other services provided to users by the cloud provider, or as otherwise known in the art. For example, services may include password-protected access to remote storage devices in the cloud via the Internet. As another example, services may include web-based hosted relational databases and scripting language middleware engines for private use by networked developers. As yet another example, services may include access to email software applications hosted on a cloud provider's website.

[0162] In some embodiments, cloud infrastructure system 1602 may include a suite of application, middleware, and database service providers delivered to customers in a self-service, subscription-based, elastically scalable, reliable, highly available, and secure manner.

[0163] In various embodiments, cloud infrastructure system 1602 can be adapted to automatically provision, manage, and track customer subscriptions to services provided by cloud infrastructure system 1602. Cloud infrastructure system 1602 can provide cloud services via different deployment models. For example, services can be provided based on a public cloud model, where cloud infrastructure system 1602 is owned by an organization selling cloud services, and the services are available to the general public or businesses in different industries. As another example, services can be provided based on a private cloud model, where cloud infrastructure system 1602 operates only for a single organization and can provide services to one or more entities within that organization. Cloud services can also be provided based on a community cloud model, where cloud infrastructure system 1602 and the services provided by cloud infrastructure system 1602 are shared by several organizations in the relevant community. Cloud services can also be provided based on a hybrid cloud model, which is a combination of two or more different models.

[0164] In some embodiments, the services provided by the cloud infrastructure system 1602 may include one or more services offered under the Software as a Service (SaaS) category, the Platform as a Service (PaaS) category, the Infrastructure as a Service (IaaS) category, or other service categories that include hybrid services. A customer may subscribe to one or more services provided by the cloud infrastructure system 1602 via a subscription order. The cloud infrastructure system 1602 then performs processing to deliver the services in the customer's subscription order.

[0165] In some embodiments, the services provided by the cloud infrastructure system 1602 may include, but are not limited to, application services, platform services, and infrastructure services. In some examples, application services may be provided by the cloud infrastructure system via a SaaS platform. The SaaS platform may be configured to provide cloud services that fall into the SaaS category. For example, the SaaS platform may provide the ability to build and deliver on-demand application suites on an integrated development and deployment platform. The SaaS platform may manage and control the underlying software and infrastructure used to provide SaaS services. By utilizing the services provided by the SaaS platform, customers can leverage applications running on the cloud infrastructure system. Customers can obtain application services without having to purchase separate licenses and support. A variety of different SaaS services may be provided. Examples include, but are not limited to, services providing solutions for sales performance management, enterprise integration, and business flexibility for large organizations.

[0166] In some embodiments, platform services may be provided by cloud infrastructure systems via a PaaS platform. The PaaS platform can be configured to provide cloud services that fall into the PaaS category. Examples of platform services may include, but are not limited to, services that enable organizations to integrate existing applications on a shared, public architecture and to leverage the shared services provided by the platform to build new applications. The PaaS platform can manage and control the underlying software and infrastructure used to provide PaaS services. Customers can access PaaS services provided by the cloud infrastructure system without having to purchase separate licenses and support.

[0167] By leveraging services provided by a PaaS platform, customers can employ programming languages ​​and tools supported by the cloud infrastructure system and also control the deployed services. In some embodiments, the platform services provided by the cloud infrastructure system may include database cloud services, middleware cloud services, and Java cloud services. In one embodiment, the database cloud service may support a shared services deployment model that enables organizations to aggregate database resources and provide database-as-a-service to customers in the form of a database cloud. The middleware cloud service can provide customers with a platform for developing and deploying various business applications, and the Java cloud service can provide customers with a platform for deploying Java applications within the cloud infrastructure system.

[0168] Various infrastructure services can be provided by IaaS platforms within cloud infrastructure systems. Infrastructure services facilitate the management and control of underlying computing resources (such as storage devices, networks, and other basic computing resources) for customers to utilize services provided by SaaS and PaaS platforms.

[0169] In some embodiments, the cloud infrastructure system 1602 may also include infrastructure resources 1630 for providing resources to customers of the cloud infrastructure system for delivering various services. In one embodiment, infrastructure resources 1630 may include a combination of pre-integrated and optimized hardware (such as servers, storage devices, and networking resources) to perform services provided by PaaS platforms and SaaS platforms.

[0170] In some embodiments, resources in the cloud infrastructure system 1602 can be shared by multiple users and dynamically reallocated as needed. Furthermore, resources can be allocated to users in different time zones. For example, the cloud infrastructure system 1602 can enable a first group of users in a first time zone to utilize the resources of the cloud infrastructure system for a specified number of hours, and then enable the same resources to be reallocated to another group of users located in a different time zone, thereby maximizing resource utilization.

[0171] In some embodiments, multiple internal shared services 1632 may be provided, shared by different components or modules of the cloud infrastructure system 1602 and services provided by the cloud infrastructure system 1602. These internal shared services may include, but are not limited to: security and identity services, integration services, enterprise repository services, enterprise manager services, virus scanning and whitelisting services, high availability, backup and recovery services, cloud-enabled services, email services, notification services, file transfer services, etc.

[0172] In some embodiments, cloud infrastructure system 1602 can provide comprehensive management of cloud services (e.g., SaaS, PaaS, and IaaS services) within the cloud infrastructure system. In one embodiment, cloud management functionality may include the ability to provision, manage, and track customer subscriptions received by cloud infrastructure system 1602.

[0173] In one embodiment, as illustrated in the figure, cloud management functionality may be provided by one or more modules, such as an order management module 1620, an order orchestration module 1622, an order supply module 1624, an order management and monitoring module 1626, and an identity management module 1628. These modules may include or be provided using one or more computers and / or servers, which may be general-purpose computers, dedicated server computers, server farms, server clusters, or any other suitable arrangement and / or combination.

[0174] In operation 1634, a customer using a client device (such as client device 1604, 1606, or 1608) can interact with the cloud infrastructure system 1602 by requesting one or more services provided by the cloud infrastructure system 1602 and placing an order to subscribe to one or more services offered by the cloud infrastructure system 1602. In some embodiments, the customer can access a cloud user interface (UI), cloud UI 1612, cloud UI 1614, and / or cloud UI 1616 and place subscription orders through these UIs. Order information received by the cloud infrastructure system 1602 in response to a customer placing an order may include information identifying the customer and the one or more services offered by the cloud infrastructure system 1602 that the customer wishes to subscribe to.

[0175] After a customer places an order, order information is received via cloud UIs 1612, 1614, and / or 1616. At operation 1636, the order is stored in the order database 1618. The order database 1618 can be one of several databases operated by the cloud infrastructure system 1618 and along with other system components. At operation 1638, the order information is forwarded to the order management module 1620. In some cases, the order management module 1620 can be configured to perform order-related billing and accounting functions, such as verifying the order and, after verification, reserving the order. At operation 1640, information about the order is transmitted to the order orchestration module 1622. The order orchestration module 1622 can utilize the order information to orchestrate services and resource provisioning for the customer's order. In some cases, the order orchestration module 1622 can use the services of the order provisioning module 1624 to orchestrate resource provisioning to support the subscribed service.

[0176] In some embodiments, the order orchestration module 1622 enables the management of business processes associated with each order and applies business logic to determine whether an order should be made available for delivery. At operation 1642, upon receiving a new subscription order, the order orchestration module 1622 sends a request to the order delivery module 1624 to allocate resources and configure those resources required to fulfill the subscription order. The order delivery module 1624 enables the allocation of resources for services ordered by customers. The order delivery module 1624 provides an abstraction layer between the cloud services provided by the cloud infrastructure system 1602 and the physical implementation layer for supplying resources for providing the requested services. Therefore, the order orchestration module 1622 can be isolated from implementation details, such as whether services and resources are actually provided on demand or pre-supplied and allocated / assigned only upon request.

[0177] At operation 1644, once services and resources are supplied, notifications of the services provided can be sent to customers on client devices 1604, 1606, and / or 1608 via the order provisioning module 1624 of the cloud infrastructure system 1602.

[0178] At operation 1646, the order management and monitoring module 1626 can manage and track customer subscription orders. In some cases, the order management and monitoring module 1626 can be configured to collect service usage statistics from subscription orders, such as storage usage, data transfer volume, number of users, system uptime, and system downtime.

[0179] In some embodiments, the cloud infrastructure system 1602 may include an identity management module 1628. The identity management module 1628 may be configured to provide identity services, such as access management and authorization services within the cloud infrastructure system 1602. In some embodiments, the identity management module 1628 may control information about customers who wish to utilize services provided by the cloud infrastructure system 1602. Such information may include information authenticating the identities of these customers and information describing what actions these customers are authorized to perform relative to various system resources (e.g., files, directories, applications, communication ports, memory segments, etc.). The identity management module 1628 may also include management of descriptive information about each customer and how and by whom this descriptive information is accessed and modified.

[0180] In the foregoing specification, the invention has been described with reference to specific embodiments thereof. However, it will be apparent that various modifications and changes can be made thereto without departing from the broader spirit and scope of the invention. For example, the above-described process flow is described with reference to a specific order of process actions. However, the order of many described process actions can be changed without affecting the scope or operation of the invention. Therefore, the specification and drawings are to be considered illustrative rather than restrictive.

Claims

1. A method comprising: Generate multiple candidate partition schemes for a database table containing multiple rows and multiple columns, wherein a given candidate partition scheme among the multiple candidate partition schemes is at least partially based on cardinality data, the cardinality data corresponding to the number of distinct values ​​in a specific column among the multiple columns; Create empty partitions for at least some of the multiple candidate partitioning schemes; Generate comprehensive statistics for empty partitions, wherein the comprehensive statistics are based at least in part on the correlation between the plurality of rows of the database table and the partitions of the candidate partitioning scheme, and the comprehensive statistics include estimated row data for empty partitions of the candidate partitioning scheme; as well as Candidate partitioning schemes are evaluated, at least in part, based on the comprehensive statistical information, to identify the selected partitioning scheme for the database table.

2. The method of claim 1, further comprising implementing the selected partitioning scheme in a production database environment and running production workloads against the partitioned tables in the production database environment.

3. The method of claim 1, wherein the candidate partitioning scheme is generated in the following manner: Identify queries within production workloads; Analyze the query to identify the predicate data of the query; Using predicate data and cardinality data to identify candidate partitioning schemes; and The candidate partitioning schemes are scored to select one or more candidate partitioning schemes from the candidate partitioning schemes.

4. The method of claim 3, wherein the score of the candidate partitioning scheme is based on the number of predicates corresponding to each candidate partitioning scheme in the candidate partitioning scheme.

5. The method of claim 1, wherein filtering is applied to remove one or more possible candidate partitioning schemes based on cardinality data.

6. The method of claim 1, wherein statistical information is synthesized by querying a database table and analyzing the query results to determine the number of rows from the database that will be associated with a particular partition for a candidate partitioning scheme.

7. The method of claim 1, wherein the interval partitioning is logically implemented as one of the candidate partitioning schemes, wherein the list partitioning is the underlying storage structure used for interval partitioning.

8. The method of claim 7, wherein the interval partitioning is implemented using a virtual column that maintains a canonical partition key mapped to the list partition.

9. The method of claim 1, wherein the database table is at least one of an unpartitioned table or a partitioned table.

10. A system comprising components for implementing any one of the methods of claims 1-9.

11. A computer program product implemented on a computer-readable medium, the computer-readable medium having a sequence of instructions stored thereon, the sequence of instructions performing any one of the methods of claims 1-9 when executed by a processor.

12. A method comprising: Identify the tables that should be logically implemented using range partitioning; Create a list partitioning structure to preserve the underlying data of the table; Generate a logical representation of the range partitions for the table, wherein the logical representation includes a virtual column storing key values ​​for range-based partitions, the key values ​​representing canonical values ​​used to identify a given interval of the list partitions, and wherein the upper bound of the given interval is selected as the canonical value of the given interval; and A mapping is performed between the list partitioning structure and the logical representation of the range partitions of the table.

13. A system comprising components for implementing the method of claim 12.

14. A computer program product implemented on a computer-readable medium, the computer-readable medium having a sequence of instructions stored thereon, the sequence of instructions executing, when executed by a processor, the method according to claim 12.

Images