Autonomously partitioning database tables
AssigneeOracle International Corporation
InventorsGeorge Eadon, Ramesh Kumar, Ananth Raghavan
Life of this patent
Estimating the term…
Abstract
What is disclosed is an improved approach to perform automatic partitioning, without requiring any expertise on the part of the user. A three stage processing pipeline is provided to generate candidate partition schemes, to evaluate the candidate using real table structures that are empty, and to then implement a selected scheme with production data for evaluation. In addition, an improved approach is described to perform automatic interval partitioning, where the inventive concept implements interval partitioning that does not impose these implicit constraints on the partition key column.
Claims24
A method, comprising:
- identifying a database table comprised of a plurality of rows and columns;
- generating a plurality of candidate partition schemes for the database table, wherein
- a given candidate partition scheme from among the plurality of candidate partition schemes is based at least in part on cardinality data that corresponds to a number of distinct values within a specific column of a plurality of columns;
- creating empty partitions for at least some candidate partition schemes of the plurality of candidate partition schemes;
- generating synthesized statistics for the empty partitions, wherein
- the synthesized statistics are based at least in part upon correlation of the plurality of rows of the database table to partitions of the candidate partition schemes, and the synthesized statistics comprise estimated row data for the empty partitions of the candidate partition schemes;
- and
- evaluating the candidate partition schemes based at least in part on the synthesized statistics to identify a selected partition scheme for the database table.
A system for partitioning in a database system, comprising:
- a processor;
- a memory for holding programmable code;
- and
- wherein the programmable code includes instructions executable by the processor for
- identifying a database table comprised of a plurality of rows and columns;
- generating a plurality of candidate partition schemes for the database table, wherein
- a given candidate partition scheme from among the plurality of candidate partition schemes is based at least in part on cardinality data that corresponds to a number of distinct values within a specific column of a plurality of columns;
- creating empty partitions for at least some candidate partition schemes of the plurality of candidate partition schemes;
- generating synthesized statistics for the empty partitions, wherein
- the synthesized statistics are based at least in part upon correlation of the plurality of rows of the database table to partitions of the candidate partition schemes, and the synthesized statistics comprise estimated row data for the empty partitions of the candidate partition schemes;
- and
- evaluating the candidate partition schemes based at least in part on the synthesized statistics to identify a selected partition scheme for the database table.
A computer program product embodied on a non-transitory computer readable medium, the computer readable medium having stored thereon a sequence of instructions, which when executed by a processor executes implementation of partitions in a database system, comprising:
- identifying a database table comprised of a plurality of rows and columns;
- generating a plurality of candidate partition schemes for the database table, wherein
- a given candidate partition scheme from among the plurality of candidate partition schemes is based at least in part on cardinality data that corresponds to a number of distinct values within a specific column of a plurality of columns;
- creating empty partitions for at least some candidate partition schemes of the plurality of candidate partition schemes;
- generating synthesized statistics for the empty partitions, wherein
- the synthesized statistics are based at least in part upon correlation of the plurality of rows of the database table to partitions of the candidate partition schemes, and the synthesized statistics comprise estimated row data for the empty partitions of the candidate partition schemes;
- and
- evaluating the candidate partition schemes based at least in part on the synthesized statistics to identify a selected partition scheme for the database table.
Dependencies are inferred from the claim text.