Ensuring no data movement when you attach a fragment

You can ensure there is no data movement when you attach a fragment by establishing identical check constraint expressions and verifying that fragment expressions are not overlapping.

To ensure that no data movement occurs when you attach a fragment:

Establish a check constraint on the attached table that is identical to the fragment expression that it will assume after the ALTER FRAGMENT ATTACH operation.
Define the fragments with nonoverlapping expressions.

For example, you might create a fragmented table and index with the following SQL statements:

CREATE TABLE tb1(a int) 
   FRAGMENT BY EXPRESSION
      (a >=0 AND a < 5) IN db1,
      (a >=5 AND a <10) IN db2;

CREATE INDEX idx1 ON tb1(a);

Suppose you create another table that is not fragmented, and you subsequently decide to attach it to the fragmented table.

CREATE TABLE tb2 (a int, check (a >=10 and a<15)) 
   IN db3; 

CREATE INDEX idx2 ON tb2(a) 
   IN db3;         

ALTER FRAGMENT ON TABLE tb1 
   ATTACH 
      tb2 AS (a >= 10 AND a<15) AFTER db2;

This ALTER FRAGMENT ATTACH operation takes advantage of the existing index idx2 because the following steps were performed in the example to prevent data movement between the existing and the new table fragment:

The check constraint expression in the CREATE TABLE tb2 statement is identical to the fragment expression for table tb2 in the ALTER FRAGMENT ATTACH statement.
The fragment expressions specified in the CREATE TABLE tb1 and the ALTER FRAGMENT ATTACH statements are not overlapping.

Therefore, the database server preserves index idx2 in dbspace db3 and converts it into a fragment of index idx1. The index idx1 remains as an index with the same fragmentation strategy as the table tb1.