System and method for automating data partitioning in a parallel database
View Patent ↗A system for automating data partitioning in a parallel database includes plural nodes connected in parallel. Each node includes a database server and two databases connected thereto. Each database server includes a query optimizer. Moreover, a partitioning advisor communicates with the database server and the query optimizer. The query optimizer and the partitioning advisor include a program for recommending and evaluating data table partitions that are useful for processing a workload of query statements. The data table partitions are recommended and evaluated without requiring the data tables to be physically repartitioned.
1. A method for automatically partitioning data in a parallel database, comprising:
recommending at least one candidate partition for at least one data table in a databased sever based in part on a workload of query statements referencing the data table;
using a query optimizer, evaluating the candidate partition based in part on the workload of query statements to determine a cost of using the candidate partition by using statistics and replacing original partitions in a workload of query statements with candidate partitions and adjusting at least some of the statistics used in the evaluating act least in part by:
determining a ratio between a number of nodes in a prior partition and a number of nodes in a new partition to generate an output; and
at least in part using the output, adjusting a number of index levels.
2. The method of claim 1 , further comprising:
determining a best candidate partition for at least one data table and a corresponding cost at least partially based on the best candidate partition.
3. The method of claim 2 , further comprising: combining the candidate partition with at least one other candidate partition; and
evaluating a combination of candidate partitions to determine a cost using the combination.
4. The method of claim 1 , wherein the candidate partition is recommended by:
computing a list of candidate partitions for each base table of a database that can reduce a cost of processing at least one query statement.
5. The method of claim 4 , further comprising: reducing the number of candidate partitions for each base table without losing plan quality.
6. The method of claim 1 , further comprising:
evaluating the workload of query statements with candidate partitions in place of the original partitions.
7. The method of claim 1 , wherein the candidate partition is recommended at least partially based on a predetermined cost model and the candidate partition is evaluated at least partially based on the predetermined cost model.