Techniques for partition pruning

• US 7,814,104 B2
• Filed: 05/04/2005
• Issued: 10/12/2010
• Est. Priority Date: 05/04/2005
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Shankar, Shrikanth, Raghavan, Ananth
• Primary Examiner(s): Mahmoudi; Tony
• Assistant Examiner(s): Truong; Dennis
• Application Number:
  US11/124,454
• Publication Number:
  US 20060253429A1
• Time in Patent Office: 1,987 Days
• Field of Search: 707/2, 707/3, 707/5, 707100-102, 707/1, 707/736, 707/737, 707/794, 707/696, 706 12- 14, 706 45- 61
• US Class Current: 707/737
• CPC Class Codes: G06F 16/24557 Efficient disk access during query execution

Abstract

Techniques are described for performing partition pruning in situations where previous database servers could not. Specifically, techniques are provided for (1) performing partition pruning based on predicates that involve non-prefix subsets of composite partitioning keys, (2) performing partition pruning when a global index access path is used to execute a query, and (3) performing partition pruning when predicates on partitioning columns are OR-ed together. More generally, techniques are described for partition pruning based on a non-contiguous set of not-pruned partitions.

Claims

1. A computer-implemented method of partition pruning, comprising:

receiving a query that requests retrieval of data items from a partitioned object;

wherein said partitioned object is partitioned based on a composite key;

wherein the query includes a predicate that specifies a condition involving a constituent key of said composite key;

wherein said constituent key does not constitute a prefix of said composite key;

for each partition of a plurality of partitions of said partitioned object, performing the steps of

(a) determining a constituent-key-range for said constituent key;

(b) based on the constituent-key-range, determining whether it is possible for the partition to include key values that satisfy the condition; and

(c) if it is not possible for the partition to include key values that satisfy the condition, then pruning the partition to exclude the partition from consideration during execution of the query; and

wherein each partition of said plurality of partitions does not include any defined subpartitions;

wherein the method is performed by one or more computing devices.

2. The method of claim 1 wherein the step of pruning said partition results in a non-contiguous set of not-pruned partitions.

3. The method of claim 1 wherein:

the method further comprises determining a not-pruned range of partitions during compilation of the query;

the plurality of partitions are those partitions that fall within the not-pruned range; and

the steps of (a), (b) and (c) are performed during execution of the query.

4. A computer-implemented method of partition pruning, comprising:

during execution of a query that specifies a first condition that involves a first key, using a global index built on the first key to return rowids of rows that belong to a plurality of partitions of a partitioned table; and

wherein the partitioned table is partitioned based on a second key;

determining a set of not-pruned partitions based on a second condition, of the query, that involves the second key;

for each rowid returned by said global index, performing the steps of;

determining whether the rowid maps to a partition, of the plurality of partitions, that does not belong to said set of not-pruned partitions; and

if the rowid maps to a partition, of the plurality of partitions, that does not belong to said set of not-pruned partitions, then discarding the rowid without probing into the partition;

wherein the method is performed by one or more computing devices.

5. One or more machine-readable storage media storing instructions which, when executed by one or more processors, cause:

receiving a query that requests retrieval of data items from a partitioned object;

wherein said partitioned object is partitioned based on a composite key;

wherein the query includes a predicate that specifies a condition involving a constituent key of said composite key;

wherein said constituent key does not constitute a prefix of said composite key;

for each partition of a plurality of partitions of said partitioned object, performing the steps of

(a) determining a constituent-key-range for said constituent key;

(b) based on the constituent-key-range, determining whether it is possible for the partition to include key values that satisfy the condition; and

(c) if it is not possible for the partition to include key values that satisfy the condition, then pruning the partition to exclude the partition from consideration during execution of the query; and

wherein each partition of said plurality of partitions does not include any defined subpartitions.

6. The one or more machine-readable storage media of claim 5, wherein the step of pruning said partition results in a non-contiguous set of not-pruned partitions.

7. The one or more machine-readable storage media of claim 5, wherein:

the instructions, when executed by the one or more processors, further cause determining a not-pruned range of partitions during compilation of the query;

the plurality of partitions are those partitions that fall within the not-pruned range; and

the steps of (a), (b) and (c) are performed during execution of the query.

8. One or more machine-readable storage media storing instructions which, when executed by one or more processors, cause:

during execution of a query that specifies a first condition that involves a first key, using a global index built on the first key to return rowids of rows that belong to a plurality of partitions of a partitioned table; and

wherein the partitioned table is partitioned based on a second key;

determining a set of not-pruned partitions based on a second condition, of the query, that involves the second key;

for each rowid returned by said global index, performing the steps of;

determining whether the rowid maps to a partition, of the plurality of partitions, that does not belong to said set of not-pruned partitions; and

if the rowid maps to a partition, of the plurality of partitions, that does not belong to said set of not-pruned partitions, then discarding the rowid without probing into the partition.