Troubleshooting
Problem
When restrictions are applied to columns that are zone mapped, zone maps greatly reduce the amount of disk scanned. Ordering your data on columns most frequently used in restrictions or joins can significantly reduce scanning these tables by increasing the selectivity of zone maps.
Diagnosing The Problem
Zone maps are somewhat similar to indexes; however, instead of relating to individual rows, they relate to allocations of disk referred to as extents. An extent is a 3 MB allocation of disk; this is the smallest unit that can be allocated to a table. As the table grows and fills up an extent on a SPU, a new extent is allocated.
Zone maps contain the minimum and maximum value for some columns within a particular extent. When a query has a restriction in it that specifies a column that has zone map information on it, the system will first create a unique list of all the extents where the restriction value falls between the minimum and maximum value found on the extent. If the table is ordered on columns frequently involved in restrictions, the number of extents that must be scanned is greatly reduced — thus reducing the time necessary to scan the table.
Resolving The Problem
nz_zonemap is a utility that is part of the Netezza Support Contributed Toolkit. If it is not installed, refer to the related document. Database: TPCDS40B ZoneMaps are enabled on this Table/MView Extent # | INV_DATE_SK (Min) | INV_DATE_SK (Max) | ORDER'ed
To identify whether a table is ordered on a given column, run nz_zonemap as follows:
[nz@cs-spubox6 ~]$ nz_zonemap TPCDS40B INVENTORY INV_DATE_SK
Object Name: INVENTORY
Object Type: TABLE
Object ID : 560485
Data Slice: 1
Datatype 1: INV_DATE_SK (INTEGER)
----------+-------------------+-------------------+----------
0 | 2450749 | 2450749 |
1 | 2450749 | 2450751 | TRUE
2 | 2450751 | 2450753 | TRUE
3 | 2450753 | 2450753 | TRUE
4 | 2450753 | 2450756 | TRUE
5 | 2450756 | 2450758 | TRUE
6 | 2450758 | 2450760 | TRUE
7 | 2450760 | 2450760 | TRUE
8 | 2450760 | 2450763 | TRUE
9 | 2450763 | 2450765 | TRUE
10 | 2450765 | 2450767 | TRUE
11 | 2450767 | 2450767 | TRUE
12 | 2450767 | 2450770 | TRUE
13 | 2450770 | 2450772 | TRUE
14 | 2450772 | 2450774 | TRUE
15 | 2450774 | 2450774 | TRUE
16 | 2450774 | 2450777 | TRUE
17 | 2450777 | 2450779 | TRUE
...
...
...
608 | 2452177 | 2452184 | TRUE
609 | 2452184 | 2452191 | TRUE
610 | 2452191 | 2452198 | TRUE
611 | 2452198 | 2452205 | TRUE
612 | 2452205 | 2452209 | TRUE
(613 rows)
The TRUE indicator found in the fourth column indicates that this table is ordered on the column specified,INV_DATE_SK.
Using Additional Options
By default, nz_zonemap scans data slice 1. To scan a different data slice, add the -dsid option as follows:
[nz@cs-spubox6 ~]$ nz_zonemap -dsid 2 TPCDS40B INVENTORY INV_DATE_SK | head
Database: TPCDS40B
Object Name: INVENTORY
Object Type: TABLE
Object ID : 560485
Data Slice: 2
Datatype 1: INV_DATE_SK (INTEGER)
Add the -fast option to reduce the processing time by approximately one half. The output will not include the TRUE/FALSE indicator so you must visually scan the rows to determine if the minimum and maximum values are contiguous. For example:
[nz@cs-spubox6 ~]$ nz_zonemap -dsid 2 -fast TPCDS40B INVENTORY INV_DATE_SK | more
Database: TPCDS40B
Object Name: INVENTORY
Object Type: TABLE
Object ID : 560485
Data Slice: 2
Datatype 1: INV_DATE_SK (INTEGER)
ZoneMaps are enabled on this Table/MView
Extent # | INV_DATE_SK (Min) | INV_DATE_SK (Max)
----------+-------------------+-------------------
0 | 2450749 | 2450751
1 | 2450751 | 2450753
2 | 2450753 | 2450756
3 | 2450756 | 2450760
4 | 2450760 | 2450763
5 | 2450763 | 2450765
6 | 2450765 | 2450770
7 | 2450770 | 2450772
8 | 2450772 | 2450774
9 | 2450774 | 2450779
As expected, the nz_zonemap -fast output is missing the ordered indicator.
Historical Number
NZ938093
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg21570732