Fragmenting the index in the same way as the table

Suppose you 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 then 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 attach operation can take advantage of the existing index idx2 if no data movement occurs between the existing and the new table fragments. If no data movement occurs:

• The database server reuses index idx2 and converts it to a fragment of index idx1.
• The index idx1 remains as an index with the same fragmentation strategy as the table tb1.

If the database server discovers that one or more rows in the table tb2 belong to preexisting fragments of the table tb1, the database server:

• Drops and rebuilds the index idx1 to include the rows that were originally in tables tb1 and tb2
• Drops the index idx2