Techniques for using zone map information for post index access pruning

Patent metadata

Field	Value
Publication number	US-9514187-B2
Application number	US-201414245934-A
Country	US
Kind code	B2
Filing date	Apr 4, 2014
Priority date	Sep 28, 2012
Publication date	Dec 6, 2016
Grant date	Dec 6, 2016

Abstract

Techniques for using zone map information for post index access pruning. In one embodiment, for example, a method for using zone map information for post index access pruning comprises: receiving a query statement comprising a first filter predicate on an indexed column of a database table and a second filter predicate on a zoned column of a database table; identifying zero or more pruneable zones of a zone map based on a value for the zoned column in the second filter predicate; obtaining a set of data record addresses from an index on the indexed column based on a value for the indexed column in the first filter predicate; and pruning, from access paths for processing the query statement, any data records, corresponding to data record addresses in the set of data record addresses, that are physically located in one of the pruneable zones.

First claim

The invention claimed is:

1. A method for using zone map information for post index access pruning comprising:
receiving a query statement comprising a first filter predicate on an indexed column of a first database table and a second filter predicate on a zoned column of a second database table;
identifying one or more pruneable zones of a zone map based on a value for the zoned column in the second filter predicate;
wherein the zone map comprises a plurality of zones corresponding to a plurality of contiguous regions of disk blocks;
wherein, for each zone of the plurality of zones, the zone map stores a minimum column value for the zoned column for the zone and a maximum column value for the zoned column for the zone;
obtaining a set of data record addresses from an index on the indexed column based on a value for the indexed column in the first filter predicate; and
pruning, from access paths for processing the query statement, any data records, corresponding to data record addresses in the set of data record addresses, that are physically located in one of the one or more pruneable zones.

2. The method of claim 1 , wherein each of the data record addresses in the set of data record address is a row identifier.

3. The method of claim 1 , wherein each of the data record addresses in the set of data record addresses comprises information identifying a physical location of the corresponding data record.

4. The method of claim 1 , wherein the indexed column and the zoned column are columns of the same database table.

5. The method of claim 1 , wherein the indexed column is a column of a fact table in a star schema and the zoned column is a column of a dimension table in the star schema.

6. The method of claim 1 , wherein the set of data record addresses are obtained from the index as a stream of data record addresses; and wherein the method further comprises:
processing the stream of data record addresses including, for each data record address in the set of data record addresses, determining whether the data record corresponding to the data record address is physically located within one of the one or more pruneable zones, and pruning the corresponding data record from access paths for processing the query statement depending on whether the corresponding data record is physically located within one of the one or more pruneable zones.

7. The method of claim 1 , wherein the index on the indexed column is a B-tree index or a bitmap index.

8. The method of claim 1 , wherein the second filter predicate is an equality predicate, an inequality predicate, a range predicate, a like predicate with a constant prefix, or an in-list predicate.

9. The method of claim 1 , further comprising:
fetching, from a cache or from disk, any data records, corresponding to data record addresses in the set of data record addresses, that are not physically located in any of the one or more pruneable zones.

10. The method of claim 1 , wherein:
the zoned column is a first zoned column;
the query statement comprises a plurality of filter predicates on a zoned column, each on a different zoned column, the plurality of filter predicates comprising the second filter predicate on the first zoned column; and
identifying the one or more pruneable zones of the zone map is based on the values for the different zoned columns in the plurality of filter predicates on a zoned column.

11. One or more non-transitory computer-readable media storing instructions which, when executed by one or more computing devices, causes the one or more computing devices to perform steps for using zone map information for post index access pruning comprising:
receiving a query statement comprising a first filter predicate on an indexed column of a first database table and a second filter predicate on a zoned column of a second database table;
identifying one or more pruneable zones of a zone map based on a value for the zoned column in the second filter predicate;
wherein the zone map comprises a plurality of zones corresponding to a plurality of contiguous regions of disk blocks;
wherein, for each zone of the plurality of zones, the zone map stores a minimum column value for the zoned column for the zone and a maximum column value for the zoned column for the zone;
obtaining a set of data record addresses from an index on the indexed column based on a value for the indexed column in the first filter predicate; and
pruning, from access paths for processing the query statement, any data records, corresponding to data record addresses in the set of data record addresses, that are physically located in one of the one or more pruneable zones.

12. The one or more non-transitory computer-readable media of claim 11 , wherein each of the data record addresses in the set of data record address is a row identifier.

13. The one or more non-transitory computer-readable media of claim 11 , wherein each of the data record addresses in the set of data record addresses comprises information identifying a physical location of the corresponding data record.

14. The one or more non-transitory computer-readable media of claim 11 , wherein the indexed column and the zoned column are columns of the same database table.

15. The one or more non-transitory computer-readable media of claim 11 , wherein the indexed column is a column of a fact table in a star schema and the zoned column is a column of a dimension table in the star schema.

16. The one or more non-transitory computer-readable media of claim 11 , wherein the set of data record addresses are obtained from the index as a stream of data record addresses; and wherein the steps further comprise:
processing the stream of data record addresses including, for each data record address in the set of data record addresses, determining whether the data record corresponding to the data record address is physically located within one of the one or more pruneable zones, and pruning the corresponding data record from access paths for processing the query statement depending on whether the corresponding data record is physically located within one of the one or more pruneable zones.

17. The one or more non-transitory computer-readable media of claim 11 , wherein the index on the indexed column is a B-tree index or a bitmap index.

18. The one or more non-transitory computer-readable media of claim 11 , wherein the second filter predicate is an equality predicate, an inequality predicate, a range predicate, a like predicate with a constant prefix, or an in-list predicate.

19. The one or more non-transitory computer-readable media of claim 11 , the steps further comprising:
fetching, from a cache or from disk, any data records, corresponding to data record addresses in the set of data record addresses, that are not physically located in any of the pruneable zones.

20. The one or more non-transitory computer-readable media of claim 11 , wherein:
the zoned column is a first zoned column;
the query statement comprises a plurality of filter predicates on a zoned column, each on a different zoned column, the plurality of filter predicates comprising the second filter predicate on the first zoned column; and
the step of identifying the one or more pruneable zones of the zone map is based on the values for the different zoned columns in the plurality of filter predicates on a zoned column.

21. A method for using zone map information for post index access pruning comprising:
receiving a query statement comprising a first fi

Assignees

• Oracle Int Corp

Inventors

• Ziauddin Mohamed

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