Fragmentation Strategies

A fragmentation strategy consists of two parts:

• A distribution scheme

  The scheme that you use to group rows or index keys into fragments is called the distribution scheme. You specify the distribution scheme in the FRAGMENT BY clause of the CREATE TABLE, CREATE INDEX, or ALTER FRAGMENT statement.

• The set of dbspaces in which you locate the fragments

  You specify the set of dbspaces in the IN clause of these SQL statements.

The database server supports the following distribution schemes:

• Round-robin. This type of fragmentation places rows one after another in fragments, rotating through the series of fragments to distribute the rows evenly.

  For INSERT statements, the database server uses a hash function on a random number to determine the fragment in which to place the row. For INSERT cursors, the database server places the first row in a random fragment, and the second and subsequent rows are assigned to fragments in sequence. If one of the fragments is full, that fragment is skipped.

• Expression-based. This type of fragmentation puts rows into fragments based on a fragmentation expression that you specify. This expression defines criteria, or rules, for assigning a set of rows to each fragment. The expression can take the form of a range or some other arbitrary rule. You can specify a remainder fragment that holds all rows that do not match the criteria for any other fragment, although a remainder fragment reduces the efficiency of the expression-based distribution scheme.
• System-defined hash. This type of fragmentation uses an internal, system-defined rule that distributes rows with the object of keeping the same number of rows in each fragment.
• Hybrid fragmentation. This type of fragmentation combines two types of distribution schemes to put rows into fragments in different dbslices and dbspaces. You specify an expression-based distribution scheme to choose a dbslice, and a system-defined hash distribution scheme to fragment the table across dbspaces within that dbslice.

Table Fragmentation

Formulating a fragmentation strategy for a table requires you to make the following decisions:

1. Decide what your primary fragmentation goal is.

   Your fragmentation goals depend, to a large extent, on the types of applications that access the table.

2. Decide how the table should be fragmented.

   You must make the following decisions:

   • Whether to fragment the table data, the table index, or both

     This decision is usually based on your primary fragmentation goal.

   • What the ideal distribution of rows or index keys is for the table

     This decision is also based on your primary fragmentation goal.

3. Decide on a distribution scheme.
4. To complete the fragmentation strategy, you must decide on the number and location of the fragments.

For more information on the decisions that you must make to formulate a fragmentation strategy, see the IBM Informix: Database Design and Implementation Guide. For information on optimizing the performance of your fragmentation scheme, refer to your IBM Informix: Performance Guide.

Temporary Table Fragmentation

Just as you fragment permanent tables, you also can fragment an explicit temporary table across multiple disks.

To create a temporary, fragmented table, use the TEMP or SCRATCH keyword of the CREATE TABLE statement.

You can specify what distribution scheme and which dbspaces to use for the temporary table. For more information on the types of temporary tables, refer to Temporary Tables.

You can define your own fragmentation strategy for an explicit temporary table, or you can let the database server dynamically determine the fragmentation strategy. For more information, refer to your IBM Informix: Performance Guide.

Table Index Fragmentation

You can fragment both table data and table indexes. When you create an index, you can:

• Create an attached index by omitting the storage specification from the CREATE INDEX statement.

  When you do, the attached index takes on the same fragmentation strategy as the table. Each fragment of an attached index resides in the same dbspace as the corresponding table data.

  You create an attached index by omitting the FRAGMENT BY and IN clauses from the CREATE INDEX statement.

  CREATE TABLE tb1 (a int) 
     FRAGMENT BY EXPRESSION
        (a >= 0 and a < 5) IN dbspace1,
        (a >= 5 and a < 10) IN dbspace2
  ...
  
  ;
  CREATE INDEX idx1 ON tb1(a);

• Create a detached index by including an explicit storage specification in the CREATE INDEX statement.

  When you do, the detached index uses its own fragmentation strategy, which can differ from that of the table. A fragment in a detached index can reside in a different dbspace than the corresponding table data.

  You cannot use the round-robin distribution scheme for an index. For more information on the CREATE INDEX statement, refer to the IBM Informix: Guide to SQL Syntax.

Fragmenting table data and table indexes can greatly affect performance. For detailed information on fragmenting table data and table indexes, see your IBM Informix: Performance Guide.