Oracle Concept: Chapter II - 21 (12c Supplemental Content)
Reducing I/O and CPU Costs with Zone Maps
Overview of Tables
In the previous chapter, we explored the fundamentals of Oracle databases. In this chapter, we will delve into the concept of tables and table clusters, a crucial aspect of database design. This article is a supplement to the Oracle 12c documentation, providing additional insights into the use of zone maps.
Zone Maps: A Contiguous Set of Data Blocks
A zone map is a contiguous set of data blocks, where the minimum and maximum values of a specified column are stored. When a SQL statement contains a predicate, the database will store the predicate in the zone map and compare it with the stored values during execution. This process enables the database to skip data blocks that do not satisfy the search condition, thereby reducing I/O and CPU costs associated with table scans.
District Maps: An Independent Structure
A district map is an independent structure that allows for efficient access to data blocks. Each district is mapped to a specific data block area, and the database implements this mapping as a materialized view.
Clustering and Zone Maps
When a table is specified with a clustering option, the database creates a zone map based on the clustered columns. The minimum and maximum columns of the cluster tables correspond to the same properties, allowing for efficient data access.
Creating and Maintaining District Maps
District maps can be created, deleted, and maintained using DDL statements. This flexibility enables administrators to tailor the database design to meet specific requirements.
Analogy: The Bookcase Sales Manager
To illustrate the concept of zone maps, consider a sales manager responsible for bookcase receipts. Each grid in the bookcase represents a data block, containing line items describing T-shirts sold to customers, sorted by date. A district map can be thought of as a pile of index cards, each card corresponding to a grid area (e.g., a lattice 1-10). The minimum and maximum values of the receipt date are stored on each card. When a sales manager wants to retrieve data for a specific date range, they can quickly locate the corresponding card and search only the associated grid area.
Example: Reducing Search Data with Zone Maps
Suppose we have a lineitem table with four blocks, each containing two lines of data. We can create a zone map using the CREATE MATERIALIZED ZONEMAP statement. Each zone will contain two data blocks, storing the minimum and maximum orderkey, shipdate, and receiptdate columns.
When we execute a query with a date predicate, the database can read the zone map and scan only blocks 1 and 2, since the date of January 3, 2014 falls between the minimum and maximum corresponding to the date. This efficient data access reduces I/O and CPU costs associated with table scans.
Conclusion
Zone maps are a powerful tool for reducing I/O and CPU costs associated with table scans. By understanding how to create and maintain district maps, administrators can optimize database design and improve performance. This article provides a comprehensive overview of zone maps, including their implementation and maintenance.