Improving ALTER FRAGMENT ATTACH Performance

To take advantage of these performance optimizations for the ALTER FRAGMENT ATTACH statement, you must meet all of the following requirements:

• Formulate appropriate distribution schemes for your table and index fragments.
• Ensure that no data movement occurs between the resultant partitions due to fragment expressions.
• Update statistics for all the participating tables.
• Make the indexes on the attached tables unique if the index on the surviving table is unique.

Formulating Appropriate Distribution Schemes

This section describes three distribution schemes that allow the attach operation of the ALTER FRAGMENT statement to reuse existing indexes:

• Fragment the index in the same way as the table.
• Fragment the index with the same set of fragment expressions as the table.
• Attach unfragmented tables to form a fragmented table.

Fragmenting the Index in the Same Way as the Table

You fragment an index in the same way as the table when you create an index without specifying a fragmentation strategy. A fragmentation strategy is the distribution scheme and set of dbspaces in which the fragments are located. For details, see Planning a Fragmentation Strategy.

For example, 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

For more information on how to ensure no data movement between the existing and the new table fragments, see Ensuring No Data Movement When You Attach a Fragment.

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 the 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.

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 on how to ensure no data movement between the existing and the new table fragments, see Ensuring No Data Movement When You Attach a Fragment.

Attaching Unfragmented Tables Together

You also take advantage of the performance improvements for the ALTER FRAGMENT ATTACH operation when you combine two unfragmented tables into one fragmented table.

For example, suppose you create two unfragmented tables and indexes with the following SQL statements:

CREATE TABLE tb1(a int) IN db1; 
   CREATE INDEX idx1 ON tb1(a) in db1;
CREATE TABLE tb2(a int) IN db2; 
   CREATE INDEX idx2 ON tb2(a) in db2;

You might want to combine these two unfragmented tables with the following sample distribution scheme:

ALTER FRAGMENT ON TABLE tb1 
   ATTACH 
      tb1 AS (a <= 100),
      tb2 AS (a > 100);

If no data migrates between the fragments of tb1 and tb2, the database server redefines index idx1 with the following fragmentation strategy:

CREATE INDEX idx1 ON tb1(a) F
   FRAGMENT BY EXPRESSION
      a <= 100 IN db1,
      a > 100 IN db2;

Ensuring No Data Movement When You Attach a Fragment

To ensure that no data movement occurs, take the following steps:

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.

Updating Statistics on All Participating Tables

The database server tries to reuse the indexes on the attached tables as fragments of the resultant index. However, the corresponding index on the attached table might not exist or might not be usable due to disk-format mismatches. In these cases, it might be faster to build an index on the attached tables rather than to build the entire index on the resultant table.

The database server estimates the cost to create the whole index on the resultant table. The database server then compares this cost to the cost of building the individual index fragments for the attached tables and chooses the index build with the least cost.

To ensure the correctness of the cost estimates, it is recommended that you execute the UPDATE STATISTICS statement on all of the participating tables before you attach the tables. The LOW mode of the UPDATE STATISTICS statement is sufficient to derive the appropriate statistics for the optimizer to determine cost estimates for rebuilding indexes.

Corresponding Index Does Not Exist

Suppose you create a fragmented table and index with the following SQL statements:

CREATE TABLE tb1(a int, b 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 two more tables that are not fragmented, and you subsequently decide to attach them to the fragmented table.

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

CREATE TABLE tb3 (a int, b int, check (a >= 15 and a<20)) 
   IN db4;
CREATE INDEX idx3 ON tb3(b) 
   IN db4;

UPDATE STATISTICS FOR TABLE tb1;
UPDATE STATISTICS FOR TABLE tb2;
UPDATE STATISTICS FOR TABLE tb3;

ALTER FRAGMENT ON TABLE tb1 
   ATTACH 
      tb2 AS (a >= 10 and a<15) 
      tb3 AS (a >= 15 and a<20);

In the preceding example, table tb3 does not have an index on column a that can serve as the fragment of the resultant index idx1. The database server estimates the cost of building the index fragment for column a on the consumed table tb3 and compares this cost to rebuilding the entire index for all fragments on the resultant table. The database server chooses the index build with the least cost.

Index on Table Is Not Usable

Suppose you create tables and indexes as in the previous section, but the index on the third table specifies a dbspace that the first table also uses. The following SQL statements show this scenario:

CREATE TABLE tb1(a int, b int) 
   FRAGMENT BY EXPRESSION
      (a >=0 AND a < 5) IN db1,
      (a >=5 AND a <10) IN db2;
CREATE INDEX idx1 ON tb1(a);
CREATE TABLE tb2 (a int, b int, check (a >=10 and a<15)) 
   IN db3; 
CREATE INDEX idx2 ON tb2(a) 
   IN db3;

CREATE TABLE tb3 (a int, b int, check (a >= 15 and a<20)) 
   IN db4;
CREATE INDEX idx3 ON tb3(a) 
   IN db2 ;

This example creates the index idx3 on table tb3 in the dbspace db2. As a result, index idx3 is not usable because index idx1 already has a fragment in the dbspace db2, and the fragmentation strategy does not allow more than one fragment to be specified in a given dbspace.

Again, the database server estimates the cost of building the index fragment for column a on the consumed table tb3 and compares this cost to rebuilding the entire index idx1 for all fragments on the resultant table. Then the database server chooses the index build with the least cost.