MDC Terminology

When an MDC table is created, the dimensional key (or keys) along which to cluster the table's data are specified. Each of the specified dimensions can be defined with one or more columns, the same as an index key. A dimension block index will be automatically created for each of the dimensions specified and will be used to access data quickly and efficiently along each of the specified dimensions. In addition, a block index will also be automatically created, containing all dimension key columns . The block index will be used to maintain the clustering of the data during insert and update activity, as well as for quick and efficient access to the data.

Every unique combination of the table's dimension values form a logical cell , which is physically comprised of blocks of pages, where a block is a set of consecutive pages on disk. The set of blocks that contain pages with data having the same key value of one of the dimension block indexes is called a slice . Every page of the table will be stored in only one block, and all blocks of the table will consist of the same number of pages, known as the blocking factor . The blocking factor is equal to the table space's extent size, so that the block boundaries line up with extent boundaries.

For the following table:

  CREATE TABLE MDCTABLE (   Year INT,   Nation CHAR(25),   Color VARCHAR(10),   ... )   ORGANIZE BY( Year, Nation, Color )  

there will be three dimensions: YEAR, NATION, and COLOR. This would look like Figure 5.1.

Figure 5.1. Dimensions.

A slice contains all of the blocks of data with the same dimension value. In Figure 5.2, the CANADA slice contains all of the blocks where the NATION column contains the value CANADA.

Figure 5.2. A slice of the table.

In Figure 5.3, the YELLOW slice contains all of the blocks of data where the COLOR column contains the value YELLOW.

Figure 5.3. The yellow slice.

In Figure 5.4, the 1997 slice contains all of the blocks of data where the YEAR column contains the value 1997.

Figure 5.4. The 1997 slice.

All rows where the YEAR is 1997, the NATION is CANADA, and the color is YELLOW are stored in the same cell, as shown in Figure 5.5.

Figure 5.5. A cell.

For example, the PARTS table below is organized using two dimensions, SHIPPEDDATE and GENPART. GENPART is a generated column derived from the PARTNUM column, as shown below.

  Create Table PARTS (   partnum                integer,   partname               char(30),   shippeddate            date,   quantity               float,   price                  float,   discount               float,   shipmode               char(10),   genpart                integer generated always as (partnum/100) )   ORGANIZE BY (shippeddate, genpart)  

In this case, each block in the table will contain rows having the same value for the clustering attributes, SHIPPEDDATE and GENPART. A logical view of the physical clustering of this table is shown in Figure 5.6.

Figure 5.6. Logical view of an MDC table.

This figure shows the physical layout of the PARTS table in a logical two-dimensional view. Each unique attribute value combination of the two dimensions identifies a possible cell, for example, the combination of values of 2002-11-04 and 3 identifies the bottom right corner cell. Each cell may have zero or more blocks of data. For example, the cell with the values 2002-11-04 and 3 has one block of data identified as block 22.

NOTE

In Figure 5.6, there is no block number 1 because the first block of an MDC table is reserved.

A dimension block index is created for each of the dimension keys. The dimension block index identifies the list of blocks for a given key value. For example, for SHIPPEDDATE 2002-11-04, the list of blocks is 21 and 22. In addition, a composite block index will also be created automatically if the table has more than one dimension, and it points to individual cells with block entries.