FRAGMENT BY clause for indexes

Use the FRAGMENT BY clause to redefine the storage distribution strategy of an index without redefining the index. The keywords FRAGMENT BY and PARTITION BY are synonyms in this context.

FRAGMENT BY Clause for Indexes

Expression Fragment Clause

Notes:

¹ See Interval fragment clause
² See List fragment clause


Element	Description	Restrictions	Syntax
dbspace	Dbspace that contains the fragmented information	Must specify at least one (but no more than 2,048) dbspaces of the same page size	Identifier
expr	Expression defining an index fragment	Must be unique among fragmentation expressions for the same index, and must return a Boolean value	Condition; Expression
fragment _key	Constant expression, based on a column value. The index is fragmented on this expression.	Any column must be in the current table.	Expression
part	Name that you declare here for the fragment. Default is the dbspace name.	Required for fragments in the same dbspace as another fragment of the same index. Must be unique among fragments of the same index.	Identifier

The INIT FRAGMENT BY clause for indexes of the ALTER FRAGMENT statement can accomplish any of the following operations on the storage distribution scheme of an existing index, without redefining the index:

Change an existing fragmented index to a nonfragmented index.
Change the distribution scheme of an existing fragmented index to another distribution scheme of the same expression, list, or range interval type, or to a different type of distribution scheme.
Change the interval value or the interval fragment key (or both) of a range interval distribution scheme for an existing index.

To change the interval value expression or the fragment key expression for an existing index that is fragmented by a range interval strategy, you must use the INIT FRAGMENT BY RANGE option (rather than the MODIFY clause) of the ALTER FRAGMENT statement. When you change either or both of those expressions, the Interval Fragment clause in the ALTER FRAGMENT ON INDEX statement must also define at least one range fragment.

When you use the FRAGMENT BY or PARTITION BY clause to convert an existing storage fragmentation strategy to another fragmentation strategy, HCL OneDB™ discards the existing fragmentation strategy and moves the data records to fragments that you define in the new fragmentation strategy. Data movement similarly occurs when you convert a nonfragmented index to a fragmented index, and when you convert a fragmented index to a nonfragmented index.

To convert an existing fragmented index to a nonfragmented index, you can use the INIT clause to specify IN dbspace (or else PARTITION partition IN dbspace) as the only storage specification for a previously fragmented index.

Just as for an expression-based index fragmentation scheme that the CREATE INDEX statement defines, restrictions apply to each expression (expr) that you specify in the ALTER FRAGMENT ON INDEX . . . INIT FRAGMENT BY EXPRESSION statement, including these:

Any column that the expression references must be from the current table.
Those columns must be the indexed columns, or a subset of the indexed columns.
The expression cannot reference fields of a column of type ROW.
Any data values in the expression must be from only a single row.
No subqueries, aggregates, nor CURRVAL or NEXTVAL sequence object expressions are allowed.
The built-in CURRENT, DATE, DBINFO, DBSERVERNAME, ROWID, SITENAME, SYSDATE, TODAY, CURRENT_USER, and USER expressions are not valid in the expression.

The restrictions above also apply to fragment key expressions for list and for range interval index fragmentation schemes, including fragmentation strategies that are defined in the FRAGMENT BY clause of the CREATE INDEX statement.