When data distribution is dense and uniform and the fragmentation column contains no duplicates, you can use a range distribution scheme to distribute rows evenly across dbspaces. Range distribution uses MIN and MAX values that the user specifies to determine the distribution of rows among the fragments.

The following statement includes a FRAGMENT BY RANGE clause to specify a range distribution scheme:

CREATE TABLE cust_account (cust_id INT)
   ...
   ...
FRAGMENT BY RANGE (cust_id MIN 1000 MAX 5000) 
   IN dbsp_1, dbsp_2, dbsp_3, dbsp_4)

The MIN and MAX values specify the total range of expected values in the column. You must specify a MAX value in the FRAGMENT BY RANGE clause. If you omit the MIN value, the default MIN value is 0. In the preceding example, the database server uses cust_id values to distribute table rows across four dbspaces. The database server fragments the rows as follows.

Storage Space

For Rows with Column Values

dbsp_1

1000 <= cust_id < 2000

dbsp_2

2000 <= cust_id < 3000

dbsp_3

3000 <= cust_id < 4000

dbsp_4

4000 <= cust_id < 5000

You can use range fragmentation on a single column or, in a hybrid distribution scheme, you can specify a range scheme on different columns for each FRAGMENT BY RANGE clause. For information about how to use range fragmentation in a hybrid distribution scheme, see Hybrid Distribution Scheme (XPS).