Table Fragmentation and Data Storage

The fragmentation feature gives you additional control over where the database stores data. You are not limited to specifying the locations of individual tables and indexes. You can also specify the location of table and index fragments, which are different parts of a table or index that reside on different storage spaces. You can fragment the following storage spaces:

• Dbspaces
• Sbspaces

Usually you fragment a table when you initially create it. The CREATE TABLE statement takes one of the following forms:

CREATE TABLE tablename ... FRAGMENT BY ROUND ROBIN IN dbspace1,
 dbspace2, dbspace3;

CREATE TABLE tablename ...FRAGMENT BY EXPRESSION 
   <Expression 1> in dbspace1,
   <Expression 2> in dbspace2,
   <Expression 3> in dbspace3;

The FRAGMENT BY ROUND ROBIN and FRAGMENT BY EXPRESSION keywords refer to two different distribution schemes. Both statements associate fragments with dbspaces.

When you fragment a table, you can also create multiple partitions of the table within the same dbspace, as shown in this example:

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

Figure 50 illustrates the role of fragments in specifying the location of data.

Figure 50. Dbspaces That Link Logical Units (Including Table Fragments) and Physical Units of Storage

For information on spaces and partitions, see Managing Disk Space.

For more information about fragmentation, refer to the IBM Informix Database Design and Implementation Guide and the IBM Informix Dynamic Server Performance Guide.