A zone is a set of a contiguous data blocks on disk.
A zone map is an index-like structure built on a table and stores information about the zones of that table.
There are 2 major differences between indexes and zone maps:
– A zone map stores information per zone instead of per row which makes it much more compact than an index
– A zone map is not actively managed the way an index is kept in sync with the DML on the table
Zone maps are closer as a concept to Exadata’s storage indexes than to B-tree indexes.
Before going into how Automatic Zone Maps work in Oracle 20c, let me explain the concept with an example. Consider a small table containing basic information about some relational databases from db-engines.com (rank, score, initial and last release, cloud based):
The RDBMS_BRANDS segment has 6 data blocks with 2 rows per block:
Let us now create the zonemap on the RDBMS_BRANDS table (on 3 columns only):
SQL> CREATE MATERIALIZED ZONEMAP rdbms_zmap ON rdbms_brands (db_engines_rank, db_engines_score, initial_release); Materialized zonemap RDBMS_ZMAP created.
We have now 3 zones and each zone contains two blocks and stores the minimum and maximum of db_engines_rank, db_engines_score and initial_release:
Next, let us run a query returning all RDBMS brands with ranking score more than 1000:
Looking at the execution plan below we see that Oracle is scanning only Zone 1 as the maximum score in all other zone is smaller than 1000:
That is how zone maps work … but what is new in Oracle 20c?
We can now enable automatic creation and maintenance of basic zone maps for both partitioned and non-partitioned tables. But for now, the creation is not available for join zone maps, IOTs, external tables or temporary tables!
In 20c, you can use the new package DBMS_AUTO_ZONEMAP to enable Automatic Zone Maps in the database. Automatic zone map creation is turned off by default.
These four values are allowed for the parameter AUTO_ZONEMAP_MODE:
ON: Turns on auto zone map feature completely. Both for foreground and background zone map creation and maintenance
OFF: Turns off auto zone map feature completely. Both for foreground and background zone map creation and maintenance
FOREGROUND: Turns on only for foreground zone map creation and maintenance
BACKGROUND: Turns on only for background zone map creation and maintenance
You may use the ACTIVITY_REPORT function to view auto zone map activity for a given time window. Note that the background job that performs automatic zone map processing starts once per hour and each run may last up to three hours.
SET LONG 100000 SELECT dbms_auto_zonemap.activity_report() report FROM dual;
These 2 zonemaps related views show the most important information DBAs need:
DBA_ZONEMAPS displays all zone maps in the database
DBA_ZONEMAP_MEASURES displays the measures for all zone maps in the database
On a final note: Automatic Zone Maps are available for now only on Exadata and requires the Oracle Partitioning option.
