118 DB2 UDB ESE V8 Performance Guide for High Performance OLTP and BI
Informational constraints are not enforced by the database manager during
updates to a table; they are used by the DB2 optimizer for potential query rewrite.
Informational constraints can be used to improve the performance of queries with
UNION ALL, as well as joins. It helps the optimizer rewrite outer joins as inner
joins, and provides better estimates of cardinality.
For further details on informational constraints, refer to DB2 UDB Administration
Guide: Implementation, SC09-4820.
BI environments can benefit significantly from the definition of informational
constraints since queries tend to be complex, volumes of data very large, and
data integrity tends to be enforced through rigorous ETL processes.
OLTP environments are generally inappropriate for informational constraints
since OLTP queries are very simple, and data integrity issues are paramount;
referential constraints should be implemented in such environments. The
exception could be for existing OLTP applications that have implemented
user-defined referential integrity, and could benefit from informational constraints
for query optimization.
3.3.2 MDC design considerations
An MDC is a table type that allows data to be independently clustered along
more than one key
, unlike “regular” tables, which can have their data clustered
only according to a single key. Therefore, scans of an MDC table via any of the
dimension indexes are equally efficient, unlike a regular table where only a scan
of the data via the clustering index is likely to be efficient.
Additionally with MDC tables, clustering of data according to each key is
guaranteed, therefore eliminating the need for a reorg to reestablish clustering.
Note: The DB2 optimizer can be directed to ignore an informational constraint
by specifying the DISABLE QUERY OPTIMIZATION option in the CREATE/ALTER
Important: When using informational constraints, ensure that an appropriate
process is in place to ensure the integrity of the data.
MDCs have wide applicability in “fact” tables in star schema implementations, and it is therefore
quite common to see the word “dimensions” used instead of keys.
Chapter 3. Application design and system performance considerations 119
Unlike “regular” tables, an MDC table has multiple indexes associated with it
when it is created. There is one block index per dimension, and one composite
Also created is a block map (not shown in Figure 3-1) which is used to track the
status of each block in the MDC table.
Figure 3-1 highlights the differences between clustering in a regular table and
multi-dimensional clustering in an MDC table.
Figure 3-1 Traditional RID clustering and Multidimensional clustering
DB2 manages MDC tables by block according to dimensions instead of RIDs as
implemented in clustering indexes, as shown in Figure 3-2 on page 120.
Note: This does not mean that a reorg is no longer necessary for MDCs.
reorg may still be required against an MDC table to consolidate row overflows
and unused space in extents due to deletions.
Note: A block is the smallest allocation unit of an MDC table. It is equivalent to
an extent in “regular” tables.
Prior to MDC
All indexes RECORD-based
Clustering in one dimension only
Clustering NOT guaranteed (degrades once
page free space is exhausted)
"Block indexes" are just like normal
indexes, except they have pointers to
blocks instead of individual records.
A block is a group of consecutive
pages with the same key values in all
A ll rec ord s in this b lock
are from country
product line Z, and the
Tables managed by BLOCK according to defined clustering
Each insert transparently places a row in an existing block which
satisfies all dimensions, or creates a new block
Dim ension indexes and BLOCK-based
R esu lts in m uch sm a lle r in de xe s
RECORD-based indexes also supported
Queries in clustering dim ensions only do I/Os absolutely necessary
for selected data
Multi-Dimensional Clustering (MDC)
Multi-Dimensional Clustering (MDC)
120 DB2 UDB ESE V8 Performance Guide for High Performance OLTP and BI
Figure 3-2 Row index vis-a-vis MDC block index
Figure 3-3 on page 121 presents a conceptual diagram of multi-dimensional
clustering along three dimensions: region, year, and color.
Row Indexes, Block Indexes
Row Indexes -
1 index entry per row
Block Indexes -
1 index entry per block