Techniques for partition pruning based on aggregated zone map information

Patent metadata

Field	Value
Publication number	US-9507825-B2
Application number	US-201414245909-A
Country	US
Kind code	B2
Filing date	Apr 4, 2014
Priority date	Sep 28, 2012
Publication date	Nov 29, 2016
Grant date	Nov 29, 2016

Abstract

Techniques for partition pruning based on aggregated zone map information. In one embodiment, for example, a method for pruning partitions based on aggregated zone map information comprises: receiving a query statement comprising a filter predicate on a column of a database table; and pruning one or more partitions of the database table from access paths for processing the query statement based on determining, based on aggregated zone map information associated with the one or more partitions, that the filter predicate cannot be satisfied by data stored in the one or more partitions.

First claim

The invention claimed is:

1. A method for pruning partitions based on aggregated zone map information comprising:
receiving a query statement comprising a filter predicate on a column of a first database table; and
pruning a particular table partition of a second database table from access paths for processing the query statement based on determining, based on aggregated zone map information associated with the particular table partition, that the query statement cannot be satisfied by data stored in the particular table partition, the aggregated zone map information comprising an aggregated minimum value for the column and an aggregated maximum value for the column, the aggregated minimum value being a smallest minimum value for the column among a plurality of minimum values for the column, the aggregated maximum value being a greatest maximum value for the column among a plurality of maximum values for the column, the plurality of minimum values for the column and the plurality of maximum values for the column associated with the column by a zone map on the second database table, the plurality of minimum values for the column and the plurality of maximum values for the column associated with a plurality of zones by the zone map, the plurality of zones comprising the data stored in the particular table partition.

2. The method of claim 1 , wherein the determining, based on the aggregated zone map information, that the query statement cannot be satisfied by data stored in the particular table partition comprises:
comparing a value for the column in the filter predicate to the aggregated zone map information to determine whether the query statement cannot be satisfied by data stored in the particular table partition.

3. The method of claim 1 , further comprising:
partitioning the second database table into at least the particular table partition based on values in one or more columns of a partitioning key;
wherein the column of the filter predicate is not one of the one or more columns of the partitioning key used to partition the second database table into at least the particular table partition.

4. The method of claim 1 , further comprising:
generating and storing the aggregated zone map information by aggregating zone map information from a zone map generated for the second database table.

5. The method of claim 1 , wherein the aggregated zone map information comprises an identifier of the particular table partition, a minimum column value for the column of the filter predicate, and a maximum column value for the column of the filter predicate.

6. The method of claim 1 , further comprising:
generating a query plan for executing the query statement which does not comprise instructions for scanning table data stored in the particular table partition.

7. The method of claim 1 , wherein the filter predicate is a first filter predicate; wherein the query statement comprises a second filter predicate on a second column of the second database table; wherein values in the second column in the particular table partition are indexed in a particular index partition associated with the particular table partition, the method further comprising:
pruning the particular index partition from access paths for processing the query statement based on determining, based on the aggregated zone map information associated with the particular table partition, that the query statement cannot be satisfied by data stored in the particular table partition.

8. The method of claim 1 , wherein the first database table and the second database table are the same table.

9. The method of claim 1 , wherein the first database table is a dimension table of a star schema and the second database table is a fact table of the star schema.

10. The method of claim 1 , wherein the query statement comprises a plurality of filter predicates, the method further comprising:
pruning one or more particular table partitions of the second database table from access paths for processing the query statement based on determining, based on aggregated zone map information associated with the one or more particular partitions, that the query cannot be satisfied by data stored in any of the one or more particular table partitions.

11. A method for pruning partitions comprising:
receiving a query statement comprising a filter predicate on a column of a first database table, the filter predicate comprising a value for the column;
pruning a particular partition of a second database table from access paths for processing the query statement;
wherein the column of the filter predicate is not a column of a partitioning key used for partitioning the second database table; and
wherein the particular partition is identified as pruneable based at least on (a) aggregated zone map information associated with the particular partition and (b) the value for the column in the filter predicate.

12. The method of claim 11 , wherein the first database table is a dimension table of a star schema and the second database table is a fact table of the star schema.

13. The method of claim 11 , wherein the first database table and the second database table are the same table.

14. One or more non-transitory computer-readable media storing instructions which, when executed by one or more computing devices, cause the one or more computing devices to perform operations comprising:
receiving a query statement comprising a filter predicate on a column of a first database table; and
pruning a particular table partition of a second database table from access paths for processing the query statement based on determining, based on aggregated zone map information associated with the particular table partition, that the query statement cannot be satisfied by data stored in the particular table partition, the aggregated zone map information comprising an aggregated minimum value for the column and an aggregated maximum value for the column, the aggregated minimum value being a smallest minimum value for the column among a plurality of minimum values for the column, the aggregated maximum value being a greatest maximum value for the column among a plurality of maximum values for the column, the plurality of minimum values for the column and the plurality of maximum values for the column associated with the column by a zone map on the second database table, the plurality of minimum values for the column and the plurality of maximum values for the column associated with a plurality of zones by the zone map, the plurality of zones comprising the data stored in the particular table partition.

15. The one or more non-transitory computer-readable media of claim 14 , wherein the determining, based on the aggregated zone map information, that the query statement cannot be satisfied by data stored in the particular table partition comprises:
comparing a value for the column in the filter predicate to the aggregated zone map information to determine whether the query statement cannot be satisfied by data stored in the particular table partition.

16. The one or more non-transitory computer-readable media of claim 14 , the operations further comprising:
partitioning the second database table into at least the particular table partition based on values in one or more columns of a partitioning key;
wherein the column of the filter predicate is not one of the one or more columns of the partitioning key used to partition the second database table into at least the particular table partition.

17. The one or more non-transitory computer-readable media of claim 14 , the operat

Assignees

• Oracle Int Corp

Inventors

• Baer Hermann
• Ziauddin Mohamed
• Raghavan Ananth

Classifications

• G06F16/24554 Primary

  Unary operations; Data partitioning operations · CPC title

• G06F16/2282

  Tablespace storage structures; Management thereof · CPC title

• G06F16/24542

  Plan optimisation · CPC title

• G06F17/30339

  Physics · mapped topic

• G06F17/30486 Primary

  Physics · mapped topic