Expression-Based Distribution Scheme

Home | Previous Page | Next Page Managing Databases > Table Fragmentation Strategies > Distribution Schemes for Table Fragmentation >

Expression-Based Distribution Scheme

To specify an expression-based distribution scheme, use the FRAGMENT BY EXPRESSION clause of the CREATE TABLE or CREATE INDEX statement. The following example includes a FRAGMENT BY EXPRESSION clause to create a fragmented table with an expression-based distribution scheme:

CREATE TABLE accounts (id_num INT, name char(15)) 
FRAGMENT BY EXPRESSION
id_num <= 100 IN dbspace_1,
id_num <100 AND id_num <= 200 IN dbspace_2,
id_num > 200 IN dbspace_3

When you use the FRAGMENT BY EXPRESSION clause of the CREATE TABLE statement to create a fragmented table, you must supply one condition for each fragment of the table that you are creating.

You can define range rules or arbitrary rules that indicate to the database server how rows are to be distributed to fragments. The following sections describe the different types of expression-based distribution schemes.

Range Rule

A range rule uses SQL relational and logical operators to define the boundaries of each fragment in a table. A range rule can contain the following restricted set of operators:

The relational operators >, <, >=, <=
The logical operators AND and OR
Algebraic expressions including built-in functions

A range rule can be based on a simple algebraic expression as shown in the following example. In this example, the expression is a simple reference to a column.

FRAGMENT BY EXPRESSION
id_num > 0  AND id_num <= 20 IN dbsp1,
id_num > 20 AND id_num <= 40 IN dbsp2,
id_num > 40 IN dbsp3

The expression in a range rule can be a conjunction or disjunction of more algebraic expressions. The next example shows two algebraic expressions used to define two sets of ranges. The first set of ranges is based on the algebraic expression: "YEAR(Died) - YEAR(Born)"; the second set of ranges is based on "MONTH(Born)."

FRAGMENT  BY  EXPRESSION
YEAR(Died) - YEAR(Born) < 21 AND MONTH(Born) >= 1  AND MONTH(Born) < 4 IN dbsp1,
YEAR(Died) - YEAR(Born) < 40 AND MONTH(Born) >= 4  AND MONTH(Born) <  7 IN dbsp2,

Arbitrary Rule

An arbitrary rule uses SQL relational and logical operators. Unlike range rules, arbitrary rules allow you to use any relational operator and any logical operator to define the rule. In addition, you can reference any number of table columns in the rule. Arbitrary rules typically include the use of the OR logical operator to group data, as the following example shows:

FRAGMENT BY EXPRESSION
zip_num = 95228 OR zip_num = 95443 IN dbsp2,
zip_num = 91120 OR zip_num = 92310 IN dbsp4,
REMAINDER IN dbsp5

Using the MOD Function (IDS)

You can use the MOD function in a FRAGMENT BY EXPRESSION clause to map each row in a table to a set of integers (hash values). The database server uses these values to determine in which fragment it will store a given row. The following example shows how you might use the MOD function in an expression-based distribution scheme:

FRAGMENT BY EXPRESSION
MOD(id_num, 3) = 0 IN dbsp1,
MOD(id_num, 3) = 1 IN dbsp2,
MOD(id_num, 3) = 2 IN dbsp3

Inserting and Updating Rows

When you insert or update a row, the database server evaluates fragment expressions, in the order specified, to see if the row belongs in any of the fragments. If so, the database server inserts or updates the row in one of the fragments. If the row does not belong in any of the fragments, the row is put into the fragment that the remainder clause specified. If the distribution scheme does not include a remainder clause, and the row does not match the criteria for any of the existing fragment expressions, the database server returns an error.