efficiently against a materialized query table than the base table or tables, 

the query executes against the materialized query table:

create table mqtitso as (select * from itsotable1) data initially 

deferred refresh deferred

In addition, DB2 UDB also supports tables for supporting clustering and query 

performance enhancement. These tables can also be used according to your 

requirements:

Materialized query table (MQT)/summary table

MQT also know as summary table in previous can also be used to improve 

the query performance. We discuss MQT in more detail in 10.6,  Materialized 

query tables (MQT)  on page 328.

Multidimensional clustering (MDC) tables

MDC table has a physical cluster on more than one key or dimension at the 

same time. The MDC table maintains its clustering over all dimensions 

automatically and continuously, thus eliminating the need to reorganize the 

table in order to restore the physical order of the data.

If you create multi dimensional clustering (MDC) tables, the performance of 

many queries might improve because the optimizer can apply additional 

optimization strategies. Some advantage of MDC tables are quicker and less 

scanning because of dimension block, faster lookup, block level index ANDing 

and ORing, and faster retrieval.

It can be created using:

db2>create table itsomdc (col1 int, col2 int, col3 int, col4 char(10))

    organize by dimensions(col1,col2,col3)

Range clustered tables (RCT)

RCT are implemented as sequential clusters of data that provide fast, direct 

access. It is a table layout scheme where each record in the table has a 

predetermined offset from the logical start of the table. Some advantages 

associated with RCT are direct and fast access, lesser maintenance, lesser 

logging, lesser locking, lesser buffer pool size, and lesser indexing. It can be 

created using:

db2> create table itsorct 

       (col1 int not null, col2 int not null, col3 char(10), col4 float)

       oraganized by key sequence(col1 starting from 1 ending at 10000)

       allow overflow

Typed table/hierarchy table

Typed tables are tables that are defined with a user defined structured type. 

With typed tables you can establish a hierarchical structure with a defined 

 Chapter 5. Database porting 

105