PARTITIONING IN VIRTUAL COLUMNS

• US 20090150336A1
• Filed: 12/06/2007
• Published: 06/11/2009
• Est. Priority Date: 12/06/2007
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Bedi, Harmeek Singh, Raghavan, Ananth, Basu, Subhransu
• Granted Patent: US 8,620,888 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• 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:

dividing a database object into a plurality of partitions,

wherein each individual partition of the plurality of partitions corresponds to a unique subset of values in a virtual column of the database object,

wherein a DBMS defines the virtual column as containing results of an expression, and

wherein the virtual column comprises values computed based on the expression.

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, further comprising:

receiving a query that references the virtual column,

determining, based on the query and the each unique subset of values in the virtual column that corresponds to the each individual partition of the plurality of partitions, a set of unnecessary partitions,

wherein the set of unnecessary partitions comprises partitions in the plurality of partitions whose values 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.

5. The computer-implemented method of claim 1, further comprising:

receiving a statement that identifies the virtual column as a column by which to partition a database object.

6. The computer-implemented method of claim 1, further comprising:

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 row in the database object where a value in the virtual column in the row 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.

7. The computer-implemented method of claim 1, further comprising:

receiving a DML statement 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 row in the database object where a value in the virtual column in the row 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 row 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.

8. A computer-implemented method for processing a statement that specifies a join between a 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 method comprising the steps of:

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 are established based on the partitioning criteria used to partition said first object,

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

wherein a DBMS defines the virtual column as containing results of an expression, and

wherein the virtual column comprises values computed based on the expression;

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.

9. A computer-readable storage medium storing instructions for partitioning a database object, the instructions including instructions which, when executed by one or more processors, cause the one or more processors to perform the steps of:

dividing a database object into a plurality of partitions,

wherein each individual partition of the plurality of partitions corresponds to a unique subset of values in a virtual column of the database object,

wherein a DBMS defines the virtual column as containing results of an expression, and

wherein the virtual column comprises values computed based on the expression.

10. The computer-readable storage medium of claim 9, wherein the unique subset of values corresponds to a unique range of values in the virtual column.

11. The computer-readable storage medium of claim 9, 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.

12. The computer-readable storage medium of claim 9, further comprising:

receiving a query that references the virtual column,

determining, based on the query and the each unique subset of values in the virtual column that corresponds to the each individual partition of the plurality of partitions, a set of unnecessary partitions,

wherein the set of unnecessary partitions comprises partitions in the plurality of partitions whose values 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.

13. The computer-readable storage medium of claim 9, further comprising:

receiving a statement that identifies the virtual column as a column by which to partition a database object.

14. The computer-readable storage medium of claim 9, further comprising:

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 row in the database object where a value in the virtual column in the row 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.

15. The computer-readable storage medium claim 9, further comprising:

receiving a DML statement 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 row in the database object where a value in the virtual column in the row 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 row 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.

16. A computer-readable storage medium storing instructions for processing a statement that specifies a join between a 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 instructions including instructions which, when executed by one or more processors, cause the one or more processors to perform the steps of:

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 are established based on the partitioning criteria used to partition said first object,

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

wherein a DBMS defines the virtual column as containing results of an expression, and

wherein the virtual column comprises values computed based on the expression;

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.