Converting a Fragmented Table to a Nonfragmented Table

Enterprise Edition Home | Express Edition Home | Previous Page | Next Page SQL Statements > ALTER FRAGMENT >

Converting a Fragmented Table to a Nonfragmented Table

You might decide that you no longer want a table to be fragmented. You can use the INIT clause to convert a fragmented table to a nonfragmented table. The following example shows the original fragmentation definition, as well as how to use the INIT clause of the ALTER FRAGMENT statement to convert the table:

CREATE TABLE checks (col1 INT, col2 INT)
   FRAGMENT BY ROUND ROBIN IN dbsp1, dbsp2, dbsp3;

ALTER FRAGMENT ON TABLE checks INIT IN dbsp1;

You must use the IN dbspace clause to place the table in a dbspace explicitly.

When you use the INIT clause to change a fragmented table to a nonfragmented table, all attached indexes become nonfragmented indexes. In addition, constraints that do not use existing user-defined indexes (detached indexes) become nonfragmented indexes. All newly nonfragmented indexes exist in the same dbspace as the new nonfragmented table.

Using the INIT clause to change a fragmented table to a nonfragmented table has no effect on the fragmentation strategy of detached indexes, nor of constraints that use detached indexes.

Use the FRAGMENT BY portion of the INIT clause to fragment an existing non-fragmented table, or to convert one fragmentation strategy to another.

FRAGMENT BY Clause for Tables:
 
|--+-FRAGMENT BY-------+---------------------------------------->
   |              (1)  |
   '-PARTITION BY------'
 
                          .-,-------.
                          V         |
>--+-ROUND ROBIN--+-IN--+---dbspace-+----+----------------------+-----+--|
   |              |     |  (2)           |                      |     |
   |              |     '--------dbslice-'                      |     |
   |              | .-,---------------------------------------. |     |
   |              | V  (1)                                    | |     |
   |              '----------+-----------------+--IN--dbspace-+-'     |
   |                         '-PARTITION--part-'                      |
   +-EXPRESSION--(--| Fragment List |--)------------------------------+
   |                 .-,------.                      .-,-------.      |
   |  (2)            V        |                      V         |      |
   +--------HASH--(----column-+--)--IN--+-(dbspace,----dbspace-+--)-+-+
   |                                    '-dbslice-------------------' |
   |            .-,------.                                            |
   |            V        |                         (2)                |
   '-HYBRID--(----column-+--)--| Expression List |--------------------'
 
Fragment List:
 
   .-,-------------------------------------------------------------.
   V                                                               |
|----+------------------------+--+-(--expr--)-------+--IN--dbspace-+--|
     |  (1)                   |  |  (3)             |
     '--------PARTITION--part-'  '--------REMAINDER-'
 
Expression List:
 
               .-,---------------------------------------------------.
               V                                                     |
|--EXPRESSION------+-expr-------------+--IN--+-dbslice-----------+---+--|
                   |  (3)             |      |    .-,-------.    |
                   '--------REMAINDER-'      |    V         |    |
                                             +-(----dbspace-+--)-+
                                             '-+---------+-------'
                                               '-dbspace-'

Notes:

Dynamic Server only
Extended Parallel Server only
If included, must be the last item in fragment list

Element	Description	Restrictions	Syntax
column	Column to which the strategy applies	Must exist in the table	Identifier
dbslice	Dbslice that contains the table fragment	Must be defined	Identifier
dbspace	Dbspace that contains the table fragment	Must specify at least 2 but no more than 2,048 dbspaces	Identifier
expr	Expression that defines a table fragment	Must evaluate to a Boolean value (`t` or `f`)	Expression
part	Name declared here for a partition of dbspace that contains the table fragment	Must be unique among names of fragments of table in dbspace	Identifier

In the HYBRID option of Extended Parallel Server, column identifies a column on which to apply the hash portion of the hybrid table fragmentation strategy. The expression can reference columns only from the current table and data values only from a single row. Subqueries, aggregates, and the built-in CURRENT, DATE, and TODAY functions are not valid in the expression.

For more information on the available fragmentation strategies for tables, see the FRAGMENT BY Clause.