Zone Maps: Analogy

For a loose analogy of zone maps, consider a sales manager who uses a bookcase of pigeonholes, which are analogous to data blocks.

dynamics 365 sales training courses malaysia

Each pigeonhole has receipts (rows) describing shirts sold to a customer, ordered by ship date. In this analogy, a zone map is like a stack of index cards. Each card corresponds to a “zone” (contiguous range) of pigeonholes, such as pigeonholes 1-10. For each zone, the card lists the minimum and maximum ship dates for the receipts stored in the zone.

dynamics 365 finance training courses malaysia

When someone wants to know which shirts shipped on a certain date, the manager flips the cards until she comes to the date range that contains the requested date, notes the pigeonhole zone, and then searches only pigeonholes in this zone for the requested receipts. In this way, the manager avoids searching every pigeonhole in the bookcase for the receipts.

dynamics 365 field service training courses malaysia

Zone Maps: Example

This example illustrates how a zone map can prune data in a query whose predicate contains a constant.

Assume you create the following lineitem table:

CopyCREATE TABLE lineitem 
  ( orderkey      NUMBER        , 
    shipdate      DATE          ,
    receiptdate   DATE          ,
    destination   VARCHAR2(50)  ,
    quantity      NUMBER        );

The table lineitem contains 4 data blocks with 2 rows per block. Table 2-3 shows the 8 rows of the table.

Table 2-3 Data Blocks for lineitem Table

Block	orderkey	shipdate	receiptdate	destination	quantity
1	1	1-1-2014	1-10-2014	San_Fran	100
1	2	1-2-2014	1-10-2014	San_Fran	200
2	3	1-3-2014	1-9-2014	San_Fran	100
2	4	1-5-2014	1-10-2014	San_Diego	100
3	5	1-10-2014	1-15-2014	San_Fran	100
3	6	1-12-2014	1-16-2014	San_Fran	200
4	7	1-13-2014	1-20-2014	San_Fran	100
4	8	1-15-2014	1-30-2014	San_Jose	100

You can use the CREATE MATERIALIZED ZONEMAP statement to create a zone map on the lineitem table. Each zone contains 2 blocks and stores the minimum and maximum of the orderkey, shipdate, and receiptdate columns. Table 2-4 shows the zone map.

dynamics 365 customer service training courses malaysia

Table 2-4 Zone Map for lineitem Table

Block Range	min orderkey	max orderkey	min shipdate	max shipdate	min receiptdate	max receiptdate
1-2	1	4	1-1-2014	1-5-2014	1-9-2014	1-10-2014
3-4	5	8	1-10-2014	1-15-2014	1-15-2014	1-30-2014

When you execute the following query, the database can read the zone map and then scan only blocks 1 and 2 because the date 1-3-2014 falls between the minimum and maximum dates:

CopySELECT * FROM lineitem WHERE shipdate = '1-3-2014';