ATTACH Clause

Use the ATTACH clause of the ALTER FRAGMENT ON TABLE statement to combine tables that have identical structures into a table with the same fragmentation strategy.

You can use this syntax, for example, to combine a fragment that has been detached from a table with an archival table that has the same distributed storage scheme.

(1)
ATTACH Clause

1  ATTACH + ,
2.1 1 surviving_table
2.1 consumed_table
1?  %AS Clause
AS Clause

1  AS? 2 PARTITION new_frag 
2.1  
2.2.1 expr
2.2.1 %List Expression
2.1? 
2.2.1! AFTER
2.2.1 BEFORE
2.1 old_frag
2.2.1 %Range Interval Expression
2.2.1 13 REMAINDER
Range Interval Expression

1 
2.1 VALUES  <  range_expr
2.1 1 VALUES
IS NULL
List Expression

1  VALUES 
2.1  (
2.2.1 + , const_expr
2.1 )
2.1 1? IS  NULL
Notes:
  • 1 Use path no more than once
  • 2 Required if another surviving_table fragment has the same name as dbspace
  • 3 Required for fragmentation by expression; optional for round-robin and list fragmentation; not valid for range interval fragmentation
Element Description Restrictions Syntax
const_expr Constant expression that defines the list of values for a fragment to store Must be a quoted string or a literal value. Each value in the list must be unique among the lists for fragments of the same object. Constant Expressions
consumed _table Table that loses its identity, to be merged with surviving_table Schema must match that of surviving _table. Cannot include serial columns, nor unique, referential, or primary key constraints. See also General Restrictions for the ATTACH Clause. Identifier
expr Expression defining which rows are stored in a fragment of a table partitioned by expression Can include only columns from the current table and only data values from a single row. See also General Restrictions for the ATTACH Clause. Condition; Expression
new_frag Name declared here for a consumed_table fragment. Default is the dbspace name. Must be unique among the names of fragments of surviving_table Identifier
old_frag Fragment or dbspace name for a surviving_table fragment Must exist. Cannot be a range or interval fragment. Identifier
range _expr Constant expression that defines the upper bound for fragment key values stored in the fragment Must be a constant literal expression that evaluates to a numeric, DATETIME, or DATE data type compatible with the data type of the fragment key expression Constant Expressions
surviving _table Table on which to modify the distribution or storage location Must exist. Cannot have any constraints. See also Restrictions on the ALTER FRAGMENT Statement. Identifier

When a new expression fragment is attached to table that is fragmented by list or by range interval, the data from the consumed table and the affected fragments in the surviving table are scanned and moved into appropriate partitions, because these strategies are not overlapping.

If the automatic mode for updating distribution statistics is enabled, and the table being attached to has fragmented distribution statistics, the database server calculates the distribution statistics of the new fragment. Stale distribution statistics of existing fragments are also recalculated at this point. This recalculation of fragment statistics runs in the background. After the database server has calculated the fragment statistics, it merges them to form table distribution statistics, and stores the results in the system catalog.

To use this clause, you must have the DBA privilege or else be the owner of the specified tables. The ATTACH clause supports the following tasks: