Fragmenting the index with the same distribution scheme as the table

You fragment an index with the same distribution scheme as the table when you create an index that uses the same fragment expressions as the table.

The database server determines if the fragment expressions are identical, based on the equivalency of the expression tree instead of the algebraic equivalence. For example, consider the following two expressions:

(col1 >= 5)
(col1 = 5 OR col1 > 5)

Although these two expressions are algebraically equivalent, they are not identical expressions.

Example of Fragmenting the Index with the Same Distribution Scheme as the Table

Suppose you create two fragmented tables and indexes with the following SQL statements:

CREATE TABLE tb1 (a INT)
   FRAGMENT BY EXPRESSION
      (a <= 10) IN tabdbspc1,
      (a <= 20) IN tabdbspc2,
      (a <= 30) IN tabdbspc3;
CREATE INDEX idx1 ON tb1 (a)
   FRAGMENT BY EXPRESSION
      (a <= 10) IN idxdbspc1,
      (a <= 20) IN idxdbspc2,
      (a <= 30) IN idxdbspc3;

CREATE TABLE tb2 (a INT CHECK a> 30 AND a<= 40) 
   IN tabdbspc4;
CREATE INDEX idx2 ON tb2(a) 
   IN idxdbspc4;

Suppose you then attach table tb2 to table tb1 with the following sample SQL statement:

ALTER FRAGMENT ON TABLE tb1 
   ATTACH tb2 AS (a <= 40);

The database server can eliminate the rebuild of index idx1 for this attach operation for the following reasons:

The fragmentation expression for index idx1 is identical to the fragmentation expression for table tb1. The database server:
- Expands the fragmentation of the index idx1 to the dbspace idxdbspc4
- Converts index idx2 to a fragment of index idx1
No rows move from one fragment to another because the CHECK constraint is identical to the resulting fragmentation expression of the attached table.
For more information about how to ensure no data movement between the existing and the new table fragments, see Ensuring no data movement when you attach a fragment.