Fragmenting by HASH (XPS)

A hash-distribution scheme distributes the rows as you insert them, so that the fragments maintain approximately the same number of rows. In this distribution scheme, the database server can eliminate fragments when it searches for a row because the hash is known internally. For example, if you have a large database, as in a data-warehousing environment, you can fragment your tables across disks that belong to different coservers. If you expect to perform many queries that scan most of the data, a system-defined hash-distribution scheme can balance the I/O processing. The following example uses eight coservers with one dbspace defined on each coserver.

CREATE TABLE customer
   (
    cust_id integer,
    descr char(45),
    level char(15),
    sale_type char(10),
    channel char(30),
    corp char(45),
    cust char(45),
    vert_mkt char(30),
    state_prov char(20),
    country char(15),
    org_cust_id char(20)
)
FRAGMENT BY HASH (cust_id) IN
      customer1_spc,
      customer2_spc,
      customer3_spc,
      customer4_spc,
      customer5_spc,
      customer6_spc,
      customer7_spc,
      customer8_spc
EXTENT SIZE 20 NEXT SIZE 16

You can also specify a dbslice. When you specify a dbslice, the database server fragments the table across the dbspaces that make up the dbslice.

Serial Columns in HASH-Distribution Schemes

If you base table fragmentation on a SERIAL or SERIAL8 column, only a hash-distribution scheme is valid. In addition, the serial column must be the only column in the hashing key. (These restrictions apply only to table distributions. Fragmentation schemes for indexes that are based on SERIAL or SERIAL8 columns are not subject to these restrictions.)

The following excerpt is from a CREATE TABLE statement:

CREATE TABLE customer
   (
    cust_id serial,
   . . .
)
FRAGMENT BY HASH (cust_id) IN customer1_spc, customer2_spc

You might notice a difference between serial-column values in fragmented and nonfragmented tables. The database server assigns serial values round-robin across fragments, so a fragment might contain values from noncontiguous ranges. For example, if there are two fragments, the first serial value is placed in the first fragment, the second serial value is placed in the second fragment, the third value is placed in the first fragment, and so on.