DETACH Clause

Use the DETACH clause of the ALTER FRAGMENT ON TABLE statement to detach a table fragment from a storage distribution scheme and place the contents into a new nonfragmented table.

This clause is not valid in ALTER FRAGMENT ON INDEX statements.

For an explanation of distribution schemes, see FRAGMENT BY clause.

DETACH Clause

DETACH [ PARTITION ] fragmentnew_table
Element Description Restrictions Syntax
fragment Named fragment or dbspace that contains the table fragment to be detached. Must exist at the time of execution. For list or range- interval fragments, the PARTITION keyword must precede fragment. Identifier
new_table Name that you declare here for a nonfragmented table that results after you execute the ALTER FRAGMENT statement. Must not exist before execution Identifier

Usage

The new table that results from executing the DETACH clause does not inherit any indexes or constraints from the original table. Only data values remain.

Similarly, the new table does not inherit any access privileges from the original table. Instead, the new table has the default privileges of any new table. For further information on default table-level privileges, see the description in the GRANT statement of Table-Level Privileges.

The DETACH clause cannot be applied to a table that has any of the following attributes:
  • a ROWID column
  • a column or columns defined as the primary key of a referential constraint
  • an Enterprise Replication replicate is defined on the table
  • a detached index (that is, an index whose storage distribution scheme is not identical to the fragmentation strategy of the table).

If you omit the PARTITION keyword, the name of the fragment must be the name of the dbspace where the fragment is stored.

In the following example, a system-generated range interval fragment sys_pt1 is detached from table T1 and placed in a new unfragmented table detacht1:
ALTER FRAGMENT ON TABLE T1 DETACH PARTITION sys_pt1 detacht1;
The next example detaches a list fragment part2 from table T2 and places its data in a new unfragmented table detacht2:
ALTER FRAGMENT ON TABLE T2 DETACH PARTITION part2 detacht2;

Distribution statistics after DETACH operations

Some ALTER FRAGMENT . . . DETACH operations can cause the database server to update the index structure of the original table. When an index is rebuilt in those cases, the database server also recalculates the associated column distributions, and these statistics are available to the query optimizer when it designs query plans for the table from which the fragment was detached:
  • For an indexed column (or for a set of columns) on which ALTER FRAGMENT . . . DETACH automatically rebuilds a B-tree index, the recalculated column distribution statistics are equivalent to distributions created by the UPDATE STATISTICS statement in HIGH mode.
  • If the rebuilt index is not a B-tree index, the automatically recalculated statistics correspond to distributions created by the UPDATE STATISTICS statement in LOW mode.

If the automatic mode for updating column-distribution statistics is enabled, and the table from which the fragment is being detached has fragment-level distribution statistics, the database server uses the statistics of the detached fragment as distribution statistics for the new table. The database server also merges the data distribution statistics of the remaining fragments to calculate new table distribution statistics for the original table, and stores these results in the sysdistrib system catalog table. This registration of distribution statistics for the new table and recalculation of table distribution statistics for the old table runs in the background.

See also the section Automatic Calculation of Distribution Statistics in the description of the CREATE INDEX statement for additional information about statistical distributions that are produced automatically when an index or constraint is created on an existing table.