Partitioning in virtual columns

• US 8,620,888 B2
• Filed: 12/06/2007
• Issued: 12/31/2013
• Est. Priority Date: 12/06/2007
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Basu, Subhransu, Bedi, Harmeek Singh, Raghavan, Ananth
• Primary Examiner(s): BROWN, SHEREE N
• Application Number:
  US11/951,933
• Publication Number:
  US 20090150336A1
• Time in Patent Office: 2,217 Days
• Field of Search: 707/1, 707/5, 707/705, 707/717, 707/687, 707/803
• US Class Current: 707/705
• CPC Class Codes: G06F 16/284 Relational databases

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.

Claims

1. 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.

2. The computer-implemented method of claim 1, wherein the unique subset of values corresponds to a unique range of values in the virtual column.

3. The computer-implemented method of claim 1, wherein the unique subset of values correspond to values in the virtual column which correspond to a same hash value produced by a hash function.

4. The computer-implemented method of claim 1, wherein the query references the virtual column by specifying the virtual column expression.

5. The computer-implemented method of claim 1, wherein the steps further comprise:

receiving a DML statement that adds one or more values to a base column on which the virtual column is based;

for each value of the one or more values,

adding, to a partition of the plurality of partitions, a particular record in the database object where a value in the virtual column in the particular record corresponds to the each value,

wherein the value in the virtual column is in the unique subset of values that corresponds to the partition of the plurality of partitions.

6. The computer-implemented method of claim 1, wherein the steps further comprise:

receiving a database command that updates one or more values in a base column on which the virtual column is based;

wherein the values in the virtual column are updated in response to updated values in the base column;

for each value in the one or more values,

determining a partition in the plurality of partitions that contains a particular record in the database object where a value in the virtual column in the particular record corresponds to the each value,

determining whether the value in the virtual column is in the unique subset of values that corresponds to the partition in the plurality of partitions,

in response to determining that the value in the virtual column is not in the unique subset of values that corresponds to the partition in the plurality of partitions, moving the particular record in the database object from the partition in the plurality of partitions to a second partition in the plurality of partitions, wherein the value in the virtual column is in the unique subset of values that corresponds to the second partition of the plurality of partitions.

13. The method of claim 1, wherein the database object is a first database object, and the steps further comprises a step of processing a statement that specifies a join between the first database object and a second database object based on a join key, where the first database object is partitioned based on the join key, the step of processing comprising:

distributing to each slave process of a plurality of slave processes a first subset of data from said first database object and a second subset of data from said second database object,

wherein said first and second subsets of data that are distributed to each slave process of the plurality of slave processes are established based on partitioning criteria used to partition said first object,

wherein the join key is the virtual column of the first database object,

causing each slave process of said plurality of slave processes to perform a join between the first subset of data assigned to the slave process and the second subset of data assigned to the slave process.

7. 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.

8. The non-transitory computer-readable storage medium of claim 7, wherein the unique subset of values corresponds to a unique range of values in the virtual column.

9. The non-transitory computer-readable storage medium of claim 7, wherein the unique subset of values correspond to values in the virtual column which correspond to a same hash value produced by a hash function.

10. The non-transitory computer-readable storage medium of claim 7, wherein the query references the virtual column by specifying the virtual column expression.

11. The non-transitory computer-readable storage medium of claim 7, wherein the steps further comprise:

receiving a DML statement that adds one or more values to a base column on which the virtual column is based;

for each value of the one or more values,

adding, to a partition of the plurality of partitions, a particular record in the database object where a value in the virtual column in the particular record corresponds to the each value,

wherein the value in the virtual column is in the unique subset of values that corresponds to the partition of the plurality of partitions.

12. The non-transitory computer-readable storage medium claim 7, wherein the steps further comprise:

receiving a database command that updates one or more values in a base column on which the virtual column is based;

wherein the values in the virtual column are updated in response to updated values in the base column;

for each value in the one or more values,

determining a partition in the plurality of partitions that contains a particular record in the database object where a value in the virtual column in the particular record corresponds to the each value,

determining whether the value in the virtual column is in the unique subset of values that corresponds to the partition in the plurality of partitions,

in response to determining that the value in the virtual column is not in the unique subset of values that corresponds to the partition in the plurality of partitions, moving the particular record in the database object from the partition in the plurality of partitions to a second partition in the plurality of partitions, wherein the value in the virtual column is in the unique subset of values that corresponds to the second partition of the plurality of partitions.

14. The non-transitory computer-readable storage medium of claim 7, wherein the database object is a first database object, and the steps further comprise a step of processing a statement that specifies a join between the first database object and a second database object based on a join key, where the first database object is partitioned based on the join key, the step of processing comprising:

distributing to each slave process of a plurality of slave processes a first subset of data from said first database object and a second subset of data from said second database object,

wherein said first and second subsets of data that are distributed to each slave process of the plurality of slave processes are established based on partitioning criteria used to partition said first object,

wherein the join key is the virtual column of the first database object,

causing each slave process of said plurality of slave processes to perform a join between the first subset of data assigned to the slave process and the second subset of data assigned to the slave process.