Techniques for partition pruning based on aggregated zone map information

• US 9,507,825 B2
• Filed: 04/04/2014
• Issued: 11/29/2016
• Est. Priority Date: 09/28/2012
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Baer, Hermann, Ziauddin, Mohamed, Raghavan, Ananth
• Primary Examiner(s): Perveen, Rehana
• Assistant Examiner(s): Khong, Alexander
• Application Number:
  US14/245,909
• Publication Number:
  US 20150286681A1
• Time in Patent Office: 970 Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/24554 Unary operations; Data partitioning operations

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.

Claims

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

18. The one or more non-transitory computer-readable media claim 14, 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.

19. The one or more non-transitory computer-readable media claim 14, the operations 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.

20. The one or more non-transitory computer-readable media claim 14, 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 operations 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.

21. The one or more non-transitory computer-readable media of claim 14, wherein the first database table and the second database table are the same table.

22. The one or more non-transitory computer-readable media of claim 14, 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.

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

24. The one or more non-transitory computer-readable media of claim 23, 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.

25. The one or more non-transitory computer-readable media of claim 23, wherein the first database table and the second database table are the same table.

26. A system for pruning partitions based on aggregated zone map information, the system comprising:

a computer having at least a processor and memory;

a query engine for receiving a query statement comprising a filter predicate on a column of a database table; and

a query engine optimizer module for pruning one or more partitions associated with a 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.

27. A system for pruning partitions, the system comprising:

a computer having at least a processor and memory;

a query engine for 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;

a query engine optimizer module for pruning partitions of a second database table from access paths for processing the query statement based at least on the value for the column in the filter predicate;

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 partitions of the second database table are identified as pruneable based at least on (a) aggregated zone map information associated with the partitions and (b) the value for the column in the filter predicate.