FRAGMENT BY Clause for Indexes
Use the FRAGMENT BY clause to create a detached index and to define its fragmentation strategy across dbspaces or partitions.
This closely resembles the syntax of the FRAGMENT BY clause for tables, but the ROUND ROBIN keywords are not supported for index fragmentation. The PARTITION BY keywords are a synonym for the FRAGMENT BY keywords in this context.
- 1 See Expression Fragment Clause
- 2 See Interval fragment clause
- 3 See List fragment clause
| Element | Description | Restrictions | Syntax |
|---|---|---|---|
| dbspace | The dbspace to store the index fragment | You can specify no more than 2,048 dbspaces. All dbspaces that store the fragments must have the same page size. | Identifier |
| fragment _key | Cast, column, or function expressions on an index column. Index is fragmented on the value of this expression. | Columns must be from the current table only. | Expression |
Here the IN keyword introduces the name of a storage space where an index fragment is to be stored. If you list multiple dbspace names after the IN keyword, use parentheses to delimit the dbspace list. All dbspaces that store the fragments must have the same page size. The parentheses around the list of fragment definitions that follow the EXPRESSION keyword are optional.
For indexes that use the same RANGE interval or LIST fragmentation strategy as their table, each fragment name that you declare after the PARTITION keyword must be the same as the identifier of the corresponding table fragment.
For attached indexes that are fragmented by a RANGE interval fragmentation strategy, if no existing table fragment is in the range of a new inserted row, the database server creates a new table fragment to store the new row, and declares a system-generated name for the new table fragment. If the table is indexed, and the index is fragmented by the same RANGE interval strategy as its table, the database server also creates a new index fragment. In this case, the index fragment has the same system-generated identifier as the corresponding table fragment. For more information about system-generated RANGE interval fragments, see Interval fragment clause.