FRAGMENT BY Clause for Tables

Use the FRAGMENT BY option of the INIT clause of the ALTER FRAGMENT statement to fragment an existing nonfragmented table, or to convert one table fragmentation strategy to another.

The PARTITION BY keywords are a synonym for the FRAGMENT BY keywords in this context.

^{(explicit id fragtabs303)
fragtabs303} ^{(explicit id fragtabs505)
fragtabs505} ^{(explicit id fragtabs606)
fragtabs606}

FRAGMENT BY Clause for Tables

{ FRAGMENT BY | PARTITION BY }

{ ROUND ROBIN { IN dbspace | PARTITIONpart INdbspace } | EXPRESSION<Fragment List> | RANGE( fragment_key) <Interval Fragment Clause>^[] | LIST( fragment_key)<List Fragment Clause> ^[] }

Fragment List

[ PARTITIONpart ] { (expr) | ^[]REMAINDER } INdbspace


Element	Description	Restrictions	Syntax
column	Column to which the strategy applies	Must exist in the table	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 of a fragment	Required for any named fragment in the same dbspace as another named fragment of the same table. Name must be unique among fragments of the same table.	Identifier

This supports the same syntax as the FRAGMENT BY (or PARTITION BY) clause of the CREATE TABLE statement. For more information on the fragmentation strategies available for tables, see the FRAGMENT BY clause of the CREATE TABLE statement.

Examples of range interval fragmentation

These examples define statement define range interval fragmentation strategies for an existing table. The following ALTER FRAGMENT statement defines three fragments of a range interval fragmentation strategy that includes no NULL fragment, where numeric column c1 is the fragmentation key:

ALTER FRAGMENT ON TABLE T1 INIT 
   FRAGMENT BY RANGE(c1) 
   INTERVAL (100+100) STORE IN (dbs3, dbs4, dbs5, dbs6, dbs7, dbs8)
      PARTITION part0 VALUES < 0    IN dbs0,
      PARTITION part1 VALUES < 1000 IN dbs1,
      PARTITION part2 VALUES < 2000 IN dbs2;

Here the interval value expression of (100+100 defines the interval fragment size as 200 within the range of column c1. No fragment is defined for fragmentation key values of 2000 or greater. If this were still the storage distribution when a row with c1 equal to or greater than 2000 is inserted, the database server automatically creates a new fragment to store that row, which was outside the range of any existing fragment. Interval partitions are stored in the dbspaces dbs2, dbs3, dbs4, dbs5, dbs6, dbs7, and dbs8 in round-robin fashion.

The following statement similarly defines three fragments of a range interval fragmentation strategy that includes no NULL fragment, and where the DATE or DATETIME column c2 is the fragmentation key:

ALTER FRAGMENT ON TABLE T1 INIT
   FRAGMENT BY RANGE(c2) 
      INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) 
         PARTITION part0 VALUES <  DATE('01/01/2009') IN dbs0,
         PARTITION part1 VALUES <  DATE('07/01/2009') IN dbs1,
         PARTITION part2 VALUES <  DATE('01/01/2010') IN dbs2;

Here the interval value expression of NUMTOYMINTERVAL(1,'MONTH') defines the interval fragment size as a single month within the range of column c2. The PARTITION list defines three fragments: part0 for December of 2008, part1 for June of 2008, and part2 for December of 2009. If rows are inserted whose c2 value is not in one of these months, the database server will create new fragments for those rows. Because no STORE IN clause is specified, the database server will store these new range interval fragments in a round-robin fashion in the dbspaces dbs0, dbs1, and dbs2 that this example lists after the IN keywords of the three PARTITION specifications.

¹ If included, must be the last item in fragment list

² See ids_sqs_2095.html#ids_sqs_2095

³ See List fragment clause