Home | Previous Page | Next Page   Managing Databases > Table Fragmentation Strategies > Creating a Fragmented Table >

Creating 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
Dynamic Server

If the my_orders table resides in a Dynamic Server database, 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
End of Dynamic Server
Extended Parallel Server

If the my_orders table resides in an Extended Parallel Server database, you might create the table with a system-defined hash distribution scheme to get even distribution across fragments. Suppose that the my_orders table has 120,000 rows, and you want to distribute rows evenly across six fragments stored in different dbspaces. You decide to use the SERIAL column order_num to define the fragments.

The following example shows how to use the order_num column to define a system-defined hash fragmentation strategy:

CREATE TABLE my_orders (order_num SERIAL, ...)
   FRAGMENT BY HASH (order_num) IN dbspace1, dbspace2, 
      dbspace3, dbspace4, dbspace5, dbspace6;

You might notice a difference between SERIAL column values in a fragmented table and unfragmented tables. Extended Parallel Server assigns SERIAL values sequentially within fragments, but fragments might contain values from noncontiguous ranges. You cannot specify what these ranges are. Extended Parallel Server controls these ranges and guarantees only that they do not overlap.

Tip:
You can store table fragments in dbspaces or dbslices on Extended Parallel Server.
End of Extended Parallel Server
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]