Partitioning in virtual columns
AssigneeOracle International Corporation
InventorsSubhransu Basu, Harmeek Singh Bedi, Ananth Raghavan
Life of this patent
Estimating the term…
Abstract
Techniques are described herein for partitioning a database table using a virtual column as the partitioning key. A virtual column is defined by the database to contain results of a defining expression. Partitioning can be performed using a variety of partitioning schemes, such as range partitioning and hash partitioning. The partitions are automatically updated to reflect changes to the values in the base columns upon which the virtual column depends. Partition pruning and parallel-wise partition joins can be performed on database tables which have been partitioned on virtual columns.
Claims14
A computer-implemented method for partitioning a database object, comprising steps of:
- receiving one or more DDL statements that define a virtual column for the database object, said one or more DDL statements specifying:
- a name for the virtual column,
- a virtual column expression, and
- that the database object is to be partitioned based on the virtual column;
- generating database metadata that:
- defines the virtual column as having said name,
- defines the virtual column as representing a result of applying the virtual column expression;
- and
- defines a plurality of partitions for the database object, wherein each individual partition of the plurality of partitions corresponds to a unique subset of values of a plurality of unique subsets of values;
- storing one or more records in the database object by, for each record of the one or more records, performing at least:
- evaluating the virtual column expression on one or more base columns of the record to obtain a value, and
- assigning the record to a particular partition of the plurality of partitions based on the value;
- receiving a query that references the virtual column;
- determining, based on the query and the plurality of unique subsets of values, a set of unnecessary partitions, wherein the set of unnecessary partitions comprises partitions in the plurality of partitions that are not needed for processing the query;
- processing the query based on partitions in the plurality of partitions which are not in the set of unnecessary partitions;
- wherein the method is performed by one or more computing devices.
A non-transitory computer-readable storage medium storing instructions for partitioning a database object which, when executed by one or more processors, cause the one or more processors to perform steps comprising:
- receiving one or more DDL statements that define a virtual column for the database object, said one or more DDL statements specifying:
- a name for the virtual column,
- a virtual column expression, and
- that the database object is to be partitioned based on the virtual column;
- generating database metadata that:
- defines the virtual column as having said name, and
- defines the virtual column as representing a result of applying the virtual column expression;
- and
- defines a plurality of partitions for the database object, wherein each individual partition of the plurality of partitions corresponds to a unique subset of values of a plurality of unique subsets of values;
- storing one or more records in the database object by, for each record of the one or more records, performing at least:
- evaluating the virtual column expression on one or more base columns of the record to obtain a value, and
- assigning the record to a particular partition of the plurality of partitions based on the value;
- receiving a query that references the virtual column;
- determining, based on the query and the plurality of unique subsets of values, a set of unnecessary partitions, wherein the set of unnecessary partitions comprises partitions in the plurality of partitions that are not needed for processing the query;
- processing the query based on partitions in the plurality of partitions which are not in the set of unnecessary partitions.
Dependencies are inferred from the claim text.