The database server uses a system-defined hash algorithm to distribute data evenly by hashing a specified key. In addition to even data distribution, system-defined hash fragmentation permits the automatic elimination of fragments for queries that use the hashed key. You can use hash fragmentation for several tables to provide fragment elimination when the tables are joined in queries and to perform more processing on the local coserver.

A system-defined hash distribution scheme is the preferred method for distributing data evenly across fragments, except in the following cases:

• Range queries are used.

  A range distribution scheme might lead to better fragment elimination and therefore better query performance.

• The specified column contains a very uneven number of duplicate values or a very small number of different values.

  Either condition can result in data skew, in which some fragments become larger than others. Data skew can lead to uneven performance because the amount of data that the database server needs to process is larger in some fragments than in other fragments.

To specify a system-defined hash distribution scheme, use the FRAGMENT BY HASH clause in the CREATE TABLE statement as follows:

CREATE TABLE new_tab (id INT, name CHAR(30)) 
   FRAGMENT BY HASH (id) IN dbspace1, dbspace2, dbspace3;

In a system-defined hash distribution scheme, specify at least two dbspaces where you want the fragments to be placed or specify a dbslice.

You can also specify a composite key for a system-defined hash distribution scheme.