ALTER FRAGMENT statement

Use the ALTER FRAGMENT statement to change the distribution strategy or the storage location of an existing table or index. This statement is an extension to the ANSI/ISO standard for the SQL language.

Syntax

(explicit id altfrag002) altfrag002 (explicit id altfrag003) altfrag003 (explicit id altfrag004) altfrag004 (explicit id altfrag005) altfrag005 (explicit id altfrag006) altfrag006 (explicit id altfrag007) altfrag007 (explicit id altfrag008) altfrag008 ALTER FRAGMENT [ []ONLINE ] { ON TABLEsurviving_table { <ATTACH Clause>[] | <DETACH Clause>[] | <INIT Clause>[] | <ADD Clause>[] | { <DROP Clause>[] | <MODIFY Clause>[] } } | ON INDEXsurviving_index { <INIT Clause>[] | <ADD Clause>[] | <DROP Clause>[] | <MODIFY Clause>[] } }
Element Description Restrictions Syntax
surviving _index Index on which to modify the distribution or storage Must exist when the statement executes Identifier
surviving _table Table on which to modify the distribution or storage Must exist. See Restrictions on the ALTER FRAGMENT Statement. Identifier

Usage

The ALTER FRAGMENT statement applies only to table fragments or index fragments that are located at the current site. No remote information is accessed or updated.

You must have the Alter privilege or the DBA privilege to change the fragmentation strategy of a table. You must have the Index privilege or the DBA privilege to alter the fragmentation strategy of an index.
Attention: This statement can cause indexes to be dropped and rebuilt. Before undertaking alter operations, check corresponding sections in your Informix® Performance Guide to review effects and strategies.

Clauses of the ALTER FRAGMENT statement support the following tasks.

Clause
Effect
ATTACH
Combines two or more tables that have the same schema into a single fragmented table
DETACH
Detaches one fragment from a fragmented table, and creates a new nonfragmented table to store the rows in the fragment.
INIT
Provides the following options:
  • Defines and initializes a fragmentation strategy on a nonfragmented table
  • Changes the order of evaluation of fragment expressions
  • Changes the fragmentation strategy of a fragmented table or index
  • Changes the storage location of an existing table
  • Moves data from an existing table fragment into a new nonfragmented table
  • Changes the storage location of fragments that the database generates for a table or index
  • Changes the fragmentation key or fragmentation expression for a table or index
ADD
Adds an additional fragment to an existing fragmentation list
DROP
Drops an existing fragment from a fragmentation list
Remove one or more dbspaces from the list of dbspaces where interval fragments are created.
MODIFY
Changes an existing interval, list, or expression-based fragmentation expression
Moves an existing fragment to a different dbspace
Replaces with a new list the current list of dbspaces where interval fragments are created.
Enables or disables automatic creation of interval fragments

Use the CREATE TABLE statement or the INIT clause of the ALTER FRAGMENT statement to create fragmented tables.

After a dbspace has been renamed successfully by the onspaces utility, only the new name can reference the renamed dbspace. The database server automatically updates existing fragmentation strategies for tables or indexes in the system catalog, however, to replace the old dbspace name with the new name. You do not need to take any additional action to update a distribution strategy or storage location that was defined using the old dbspace name, but you must use the new name if you reference the dbspace in an ALTER FRAGMENT or ALTER TABLE statement.

If you omit the optional ONLINE keyword, the ALTER FRAGMENT operation requires exclusive access and exclusive locks on all of the tables involved in the operation. If you enable the FORCE_DDL_EXEC session environment option, you can force out other transactions that have opened a table involved in an ALTER FRAGMENT ON TABLE operation, or that have placed locks on any of those tables. If the server is unable to get exclusive access and exclusive locks on the table, the server starts rolling back the transactions that are open or that have locks on the table, until the value specified with the FORCE_DDL_EXEC option is reached. (For more information, see FORCE_DDL_EXEC session environment option.)

1 See ATTACH Clause
2 See DETACH Clause
3 See INIT Clause
4 See ADD Clause
5 See DROP Clause
6 See MODIFY Clause
7 Only with ATTACH, DETACH, or MODIFY on tables fragmented by interval