Create a new fragmented table

To create a fragmented table, use the FRAGMENT BY clause of the CREATE TABLE statement.

Suppose that you want to create a fragmented table similar to the orders table of the stores_demo database. You decide on a round-robin distribution scheme with three fragments and consult with your database server administrator to set up three dbspaces, one for each of the fragments: dbspace1, dbspace2, and dbspace3. The following SQL statement creates the fragmented table:

CREATE TABLE my_orders (
   order_num      SERIAL(1001),
   order_date     DATE,
   customer_num   INT, 
   ship_instruct  CHAR(40),
   backlog        CHAR(1),
   po_num       CHAR(10),
   ship_date    DATE,
   ship_weight  DECIMAL(8,2),
   ship_charge  MONEY(6),
   paid_date    DATE,
   PRIMARY KEY (order_num),
   FOREIGN KEY (customer_num) REFERENCES customer(customer_num))
   FRAGMENT BY ROUND ROBIN IN dbspace1, dbspace2, dbspace3

Suppose you want to create multiple tables that are fragmented by round robin and that you want the number of fragments to increase automatically as the tables grow. You set the AUTOLOCATE configuration parameter or session environment variable to the number of initial fragments to create. Tables that do not include the FRAGMENT BY clause in the CREATE TABLE statement are fragmented by round-robin by default into dbspaces that are chosen by the server. By default, all dbspaces are available, but you can control the list of available dbspaces.

You might decide instead to create the table with expression-based fragmentation. Suppose that your my_orders table has 30,000 rows, and you want to distribute rows evenly across three fragments stored in dbspace1, dbspace2, and dbspace3. The following statement shows how you might use the order_num column to define an expression-based fragmentation strategy:

CREATE TABLE my_orders (order_num SERIAL, ...)
   FRAGMENT BY EXPRESSION 
      order_num < 10000 IN dbspace1,
      order_num >= 10000 and order_num < 20000 IN dbspace2, 
      order_num >= 20000 IN dbspace3