Managing Dbspace Partitions

For fragmented tables that use expression-based or round-robin distribution schemes, you can create multiple partitions, which are collections of pages for a table or index, within a single dbspace.

Suppose you are creating a fragmented table using an expression-based distribution scheme in which each expression specifies the datasets that are placed in particular fragments. You might decide to separate the data in the table with data from one month in one dbspace and data from the next 11 months in 11 other dbspaces. However, if you want to use only one dbspace for all the yearly data, you can create partitions so the data for each month is stored in a separate partition in one dbspace.

If you create a fragmented table with partitions, each row in the sysfragments system catalog contains a partition name in the Partition column. If you create a fragmented table without partitions, the name of the dbspace appears in the Partition column. The Flags column in the sysfragments catalog tells you if the fragmentation scheme has partitions.

You can create tables and indexes with partitions, and you can create, drop, and alter partition fragments using the PARTITION keyword and the partition name.

To create a fragmented table with partitions, use SQL syntax as shown in the following example:

CREATE TABLE tb1(a int) 
   FRAGMENT BY EXPRESSION
     PARTITION part1 (a >=0 AND a < 5) IN dbspace1,
     PARTITION part2 (a >=5 AND a < 10) IN dbspace1
          ...
       ;

If you created a table or index fragment containing partitions, you must use syntax containing the partition name when you use the ALTER FRAGMENT statement, as shown in the following examples.

    ALTER FRAGMENT ON TABLE tb1 INIT FRAGMENT BY EXPRESSION
    PARTITION part_1 (a >=0 AND a < 5) IN dbspace1,
    PARTITION part_2 (a >=5 AND a < 10) IN dbspace1;

ALTER FRAGMENT ON INDEX ind1 INIT FRAGMENT BY EXPRESSION 
    PARTITION part_1 (a >=0 AND a < 5) IN dbspace1,
    PARTITION part_2 (a >=5 AND a < 10) IN dbspace1;

You can use the PARTITION BY EXPRESSION clause instead of the FRAGMENT BY EXPRESSION clause in CREATE TABLE, CREATE INDEX, and ALTER FRAGMENT ON INDEX statements as shown in this example:

ALTER FRAGMENT ON INDEX idx1 INIT PARTITION BY EXPRESSION 
           PARTITION part1   (a <= 10) IN idxdbspc1,
           PARTITION part2   (a <= 20) IN idxdbspc1,
           PARTITION part3  (a <= 30) IN idxdbspc1;

Use ALTER FRAGMENT syntax to change fragmented tables and indexes that do not have partitions into tables and indexes that have partitions. For example, the syntax below shows how you might The syntax below shows how you might convert a fragmented table with multiple dbspaces into one using partitions.

CREATE TABLE t1 (c1 int) FRAGMENT BY EXPRESSION
     (c1=10) IN dbs1,
     (c1=20) IN dbs2;
ALTER FRAGMENT ON TABLE t1 MODIFY dbs2 TO PARTITION part_3 (c1=20)
 IN dbs1

The syntax below shows how you might convert a fragmented index into an index that contains partitions:

CREATE TABLE t1 (c1 int) FRAGMENT BY EXPRESSION
     (c1=10) IN dbs1, (c1=20) IN dbs2, (c1=30) IN dbs3
CREATE INDEX ind1 ON t1 (c1) FRAGMENT BY EXPRESSION
     (c1=10) IN dbs1, (c1=20) IN dbs2, (c1=30) IN dbs3

ALTER FRAGMENT ON INDEX ind1 INIT FRAGMENT BY EXPRESSION
     PARTITION part_1 (c1=10) IN dbs1, PARTITION part_2 (c1=20) IN dbs1, 
     PARTITION part_3 (c1=30) IN dbs1,

See the IBM Informix Dynamic Server Performance Guide for more information on fragmentation, including fragmentation guidelines, procedures for fragmenting indexes, procedures for creating attached and detached indexes with partitions, and examples of SQL statements used to create attached and detached indexes containing partitions.

See the IBM Informix Guide to SQL: Syntax for more syntax details, including information on partitions in GRANT FRAGMENT, REVOKE FRAGMENT statements and details for using the DROP, DETACH and MODIFY clauses of the ALTER FRAGMENT statement.