Modifying Fragmentation Strategies for XPS

• ATTACH clause
• DETACH clause
• INIT clause

Tables that use HASH fragmentation support only the INIT option.

Extended Parallel Server does not support the ADD, DROP, and MODIFY options, the ALTER FRAGMENT ON INDEX statement or explicit rowids columns. To handle add, drop, or modify operations, you can use the supported options in place of ADD, DROP, and MODIFY.

Using the INIT Clause

If changes to a fragmentation strategy require data movement, you can specify the INIT clause with an ALTER FRAGMENT ON TABLE statement. When you use the INIT clause, the database server creates a copy of the table with the new fragmentation scheme and inserts rows from the original table into the new table.

Suppose you create the following prod_info table that distributes fragments by hash on the id column because your queries typically use an equality search on the id column:

CREATE TABLE prod_info
   (id      INT,
   color    INT,
   details  CHAR(100))
FRAGMENT BY HASH(id) IN dbsl;

Suppose at some point you recognize a need to perform other important queries that specify color column values but not id values. To handle this type of scenario, you might modify the data layout of the prod_info table to allow for better fragment elimination. The following ALTER FRAGMENT statement shows how you might use an INIT clause to change from a hash to a hybrid distribution scheme:

ALTER FRAGMENT ON TABLE prod_info INIT 
   FRAGMENT BY HYBRID(id)
    EXPRESSION color = 1 IN dbsl, color = 2 IN dbsl2, ...
    REMAINDER IN dbsl8;

Using ATTACH and DETACH Clauses

If you need to move data, you can use an ALTER FRAGMENT statement with the INIT clause. Otherwise, you can use ALTER FRAGMENT with the following options to modify the expression of an existing fragment:

• Use the DETACH clause to remove the fragment whose expression you want to modify.
• Use the ATTACH clause to reattach the fragment with the new expression.

Suppose that you initially create the following fragmented table:

CREATE TABLE account (acc_num INT, ...)
   FRAGMENT BY EXPRESSION
      acc_num <= 1120 IN dbspace1,
      acc_num > 1120 AND acc_num < 2000 IN dbspace2,
      REMAINDER IN dbspace3;

The following statements modify the fragment that dbspace1 contains to ensure that no account numbers with a value less than or equal to zero are stored in the fragment:

ALTER FRAGMENT ON TABLE account DETACH dbspace1 det_tab; 
CREATE TABLE new_tab (acc_num INT, ...)
   FRAGMENT BY EXPRESSION
      acc_num > 0 AND acc_num <=1120 IN dbspace1; 
ALTER FRAGMENT ON TABLE account ATTACH account, new_tab;
INSERT INTO account SELECT * FROM det_tab;
DROP TABLE det_tab;

Using the ATTACH Clause to Add a Fragment

You can use the ATTACH clause of the ALTER FRAGMENT ON TABLE statement to add a fragment from a table. Suppose that you want to add a fragment to a table that you create with the following statement:

CREATE TABLE sales (acc_num INT, ...)
   FRAGMENT BY ROUND ROBIN IN dbspace1, dbspace2, dbspace3

To add a new fragment dbspace4 to the sales table, you first create a new table with a structure identical to sales that specifies the new fragment. You then use an ATTACH clause with the ALTER FRAGMENT statement to add the new fragment to the table. The following statements add a new fragment to the sales table:

CREATE TABLE new_tab (acc_num INT, ...) IN dbspace4; 
ALTER FRAGMENT ON TABLE sales ATTACH sales, new_tab;

After you execute the ATTACH clause, the database server fragments the sales table into four dbspaces: the three dbspaces of sales and the dbspace of new_tab. The new_tab table is consumed.

Using the DETACH Clause to Drop a Fragment

You can use the DETACH clause of the ALTER FRAGMENT ON TABLE statement to drop a fragment from a table. Suppose that you want to drop a fragment from a table that you create with the following statement:

CREATE TABLE sales (acc_num INT)...) 
   FRAGMENT BY EXPRESSION
      acc_num <= 1120 IN dbspace1,
      acc_num > 1120 AND acc_num <= 2000 IN dbspace2,
      acc_num > 2000 AND acc_num < 3000 IN dbspace3,
      REMAINDER IN dbspace4;

To drop the third fragment dbspace3 from the sales table without losing any data, execute the following statements:

ALTER FRAGMENT ON TABLE sales DETACH dbspace3 det_tab; 
INSERT INTO sales SELECT * FROM det_tab;
DROP TABLE det_tab;

The ALTER FRAGMENT statement detaches dbspace3 from the distribution scheme of the sales table and places the rows in a new table det_tab. The INSERT statement reinserts rows previously in dbspace3 into the new sales table, which now has three fragments: dbspace1, dbspace2, and dbspace4. The DROP TABLE statement drops the det_tab table because it is no longer needed.