Table and index organization for multidimensional (MDC)
and insert time clustering (ITC) tables is based on the same logical
structures as standard table organization.
Like standard tables, MDC and ITC 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 and
ITC 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 and ITC 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.
As the name implies, ITC tables cluster
data based on row insert time. The differences between MDC and ITC
tables are:
block indexes are not used for any data access,
only a single composite block index is created for the table,
and that index consists of a virtual dimension, and
the index is never chosen by the optimizer for plans because the
column it contains cannot be referenced by any SQL statement.
MDC and ITC tables can have their empty blocks released to the
table space.
