Zone maps defined

Answer

Zone maps are automatically generated, persistent, internal tables that the NPS system uses to improve the throughput and the response times of SQL queries against large, grouped, or nearly ordered date, timestamp, and integer data. This type of data is common in call records, web logs, and financial transaction databases.

These large, grouped, or nearly ordered data tables contain rolling histories of the past several months or years activity with huge volumes arriving daily. Each day, a new days worth of event rows is inserted and the oldest days worth of data is deleted. Rows are rarely, if ever, modified after insertion. Typically, one table or just a few tables comprise the majority of the total space used by the database. In addition, a large percentage of the queries are against a small subset of history, such as one or a few days, the past four Mondays, or perhaps a week or two. Query processing needs to skip the data outside the date ranges to provide good response time and to support good throughput.

Zone maps take advantage of the inherent ordering or grouping of data to reduce the disk scans required to retrieve data on restricted scan queries. The Storage Manager uses zone maps to skip portions of the tables that do not contain rows of interest and thus greatly improve query execution times and system throughput.

The NPS system automatically creates new zone maps and extends existing zone maps on each SPU when you insert, update, or load data into tables.

The system generates zone map records when statistics are gathered on a table. For every column in a table that has a data type supported by zone maps, minimum and maximum values per extent are determined and inserted into the zone map table. When a user table is truncated or dropped, the system updates the zone map and removes all records in the zone map that are associated with the table.