For a partitioned table, you can place each table partition in an individual dbspace. You may also place each column for a table partition in an individual dbspace. In general, however, individual dbspaces are recommended only for BLOB or CLOB columns or columns of CHAR, VARCHAR or VARBINARY greater than 255 bytes. For example:
CREATE TABLE tab2(
col1 INT IQ UNIQUE(65500),
col2 VARCHAR(20),
col3 CLOB PARTITION (p1 IN dsp11, p2 IN dsp12,
p3 IN dsp13),
col4 DATE,
col5 BIGINT,
col6 VARCHAR(500) PARTITION (p1 IN dsp21,
p2 IN dsp22),
PRIMARY KEY (col5) IN dsp2) IN dsp1
PARTITION BY RANGE (col4)
(p1 VALUES <= ('2006/03/31') IN dsp31,
p2 VALUES <= ('2006/06/30') IN dsp32,
p3 VALUES <= ('2006/09/30') IN dsp33
);CREATE DATE INDEX c4_date ON tab2(col4) IN dsp3;
Resulting data allocation is as follows:
Partition |
Dbspace |
Data |
|---|---|---|
p1 |
dsp11 |
FP indexes for col3 (CLOB data) |
dsp21 |
FP index for col6 (VARCHAR(500) data) |
|
dsp31 |
FP indexes for col1, col2, col4, and col5 |
|
p2 |
dsp12 |
FP for col3 (CLOB data) |
dsp22 |
FP for col6 (VARCHAR(500) data) |
|
dsp32 |
FP indexes for col1, col2, col4, and col5 |
|
p3 |
dsp13 |
FP index for col3 (CLOB data) |
dsp33 |
FP indexes for col1, col2, col4, col5, and col6 (varchar(500) data) |
|
Non-partitioned |
dsp1 |
Lookup store for col1 and other share data (for all partitions) |
Non-partitioned |
dsp2 |
Primary key HG on col5 (for all partitions) |
Non-partitioned |
dsp3 |
DATE index col4_date (for all partitions) |
