Table and index organization for multidimensional clustering
(MDC) tables is based on the same logical structures as standard table
organization.
Like standard tables, MDC tables are organized into pages that
contain rows of data divided into columns. The rows on each page are
identified by record IDs (RIDs). However, the pages for MDC tables
are grouped into extent-sized blocks. For example,
Figure 1, shows a table with
an extent size of four. The first four pages, numbered 0 through 3,
represent the first block in the table. The next four pages, numbered
4 through 7, represent the second block in the table.
Figure 1. Logical table, record, and
index structure for MDC tables
The first block contains special internal records, including the
free space control record (FSCR), that are used by the DB2® server
to manage the table. In subsequent blocks, the first page contains
the FSCR. An FSCR maps the free space for new records that exists
on each page of the block. This available free space is used when
inserting records into the table.
As the name implies, MDC tables cluster data on more than one dimension.
Each dimension is determined by a column or set of columns that you
specify in the ORGANIZE BY DIMENSIONS clause of the CREATE TABLE statement.
When you create an MDC table, the following two indexes are created
automatically:
- A dimension-block index, which contains pointers to each occupied
block for a single dimension
- A composite-block index, which contains all dimension key columns,
and which is used to maintain clustering during insert and update
activity
The optimizer considers access plans that use dimension-block
indexes when it determines the most efficient access plan for a particular
query. When queries have predicates on dimension values, the optimizer
can use the dimension-block index to identify-and fetch from-the extents
that contain these values. Because extents are physically contiguous
pages on disk, this minimizes I/O and leads to better performance.
You can also create specific RID indexes if analysis of data access
plans indicates that such indexes would improve query performance.
DB2 Version 9.7 for Linux, UNIX, and Windows
