Creating partitioned indexes
When you create a partitioned index for a partitioned table, each data partition is indexed in its own index partition. By default, the index partition is stored in same table space as the data partition it indexes. Data in the indexes is distributed based on the distribution key of the table.
Before you begin
This task assumes that your partitioned table has already been created.
About this task
Restrictions
- Indexes over nonpartitioned data
- Indexes over spatial data
- XML column path indexes (system generated)
Also, The IN clause of the CREATE INDEX statement is not supported for creating partitioned indexes. By default, index partitions are created in the same table space as the data partitions they index. To specify an alternative table space in which to store the index partition, you must use the partition-level INDEX IN clause of the CREATE TABLE statement to specify a table space for indexes on a partition-by-partition basis. If you omit this clause, the index partitions will reside in the same table space as the data partitions they index.
Procedure
- Formulate a CREATE INDEX statement for your table, using the PARTITIONED clause.
- Execute the CREATE INDEX statement from a supported Db2® interface.
Example
In this example, assume the SALES table is defined as follows:
CREATE TABLE sales(store_num INT, sales_date DATE, total_sales DECIMAL (6,2))
IN ts1
PARTITION BY RANGE(store_num)
(STARTING FROM (1) ENDING AT (100),
STARTING FROM (101) ENDING AT (150),
STARTING FROM (151) ENDING AT (200))
In
this case, the three partitions of the table SALES are stored in table space ts1
.
Any partitioned indexes created for this table will also be stored in ts1
,
because that is the table space in which each partition for this table will be stored. To
create a partitioned index on the store number, use the following
statement: CREATE INDEX StoreNum ON sales(store_num) PARTITIONED
Example 2: Choosing an alternative location for all index partitions.
In this example, assume the EMPLOYEE table is defined as follows:
CREATE TABLE employee(employee_number INT, employee_name CHAR,
job_code INT, city CHAR, salary DECIMAL (6,2))
IN ts1 INDEX IN ts2
PARTITION BY RANGE (job_code)
(STARTING FROM (1) ENDING AT (10) INDEX IN ts2,
STARTING FROM (11) ENDING AT (20) INDEX IN ts2,
STARTING FROM (21) ENDING AT (30) INDEX IN ts2)
To create a partitioned index on the job codes, use the following statement:
CREATE INDEX JobCode ON employee(job_code) PARTITIONED
In
this example, the partitions of the EMPLOYEE table are stored in table space ts1
,
however, all index partitions will be stored in ts2
.
Example 3: Indexes created in several partitions.
CREATE TABLE parts(part_number INT, manufacturer CHAR,
description CLOB, price DECIMAL (4,2)) IN ts1 INDEX in ts2
PARTITION BY RANGE (part_number)
(STARTING FROM (1) ENDING AT (10) IN ts3,
STARTING FROM (11) ENDING AT (20) INDEX IN ts1,
STARTING FROM (21) ENDING AT (30) IN ts2 INDEX IN ts4);
In
this case, the PARTS table consists of three partitions: the first is in table space
ts3
, the second in ts1
and the 3rd in ts2
. If the
following statements are
issued: CREATE INDEX partNoasc ON parts(part_number ASC) PARTITIONED
CREATE INDEX manufct on parts(manufacturer DESC) NOT PARTITIONED IN TS3;
then
two indexes are created. The first is a partitioned index to order the rows in ascending order of
part number. The first index partition is created in table space ts3
, the
second in ts1
and the third in ts4
. The second index is a
nonpartitioned index which orders the rows in descending order of the manufacturer's name. This
index is created in ts3
. Note that the IN clause is allowed in CREATE INDEX
statements for nonpartitioned indexes. Also, in this case, because the table PARTS is partitioned,
to create a nonpartitioned index, the clause NOT PARTITIONED must be included in the CREATE INDEX
statement.