Distribution Schemes for Table Fragmentation

A distribution scheme is a method that the database server uses to distribute rows or index entries to fragments. Informix database servers support the following distribution schemes:

• Expression-based. This distribution scheme puts rows that contain specified values in the same fragment. You specify a fragmentation expression that defines criteria for assigning a set of rows to each fragment, either as a range rule or some 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.
• Round-robin. This distribution scheme places rows one after another in fragments, rotating through the series of fragments to distribute the rows evenly. The database server defines the rule internally.

  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, the second in the next sequential fragment, and so on. If one of the fragments is full, it is skipped.

• Range distribution. This distribution scheme ensures that rows are fragmented evenly across dbspaces. In range distribution, the database server determines the distribution of rows among fragments based on minimum and maximum integer values that the user specifies. Use a range distribution scheme when the data distribution is both dense and uniform.
• System-defined hash. This distribution scheme uses an internal, system-defined rule that distributes rows with the objective of keeping the same number of rows in each fragment.
• Hybrid. This distribution scheme combines two distribution schemes. The primary distribution scheme chooses the dbslice. The secondary distribution scheme puts rows in specific dbspaces within the dbslice. The dbspaces usually reside on different coservers.

For complete descriptions of the SQL syntax you use to specify a distribution scheme, see the CREATE TABLE and CREATE INDEX statements in the IBM Informix: Guide to SQL Syntax. For a discussion about the performance aspects of fragmentation, refer to your IBM Informix: Performance Guide.