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
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 dbspace3If 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.