Creating a Fragmented Table from Nonfragmented Tables

You might need to convert nonfragmented tables into fragmented tables in the following circumstances:

• You have an application-implemented version of table fragmentation.

  You will probably want to convert several small tables into one large fragmented table. The following section tells you how to proceed when this is the case. Follow the instructions in the section Using More Than One Nonfragmented Table.

• You have an existing large table that you want to fragment.

  Follow the instructions in the section Using a Single Nonfragmented Table.

Remember that before you perform the conversion, you must set up an appropriate number of dbspaces to contain the newly created fragmented tables.

Using More Than One Nonfragmented Table

You can combine two or more nonfragmented tables into a single fragmented table. The nonfragmented tables must have identical table structures and must be stored in separate dbspaces. To combine nonfragmented tables, use the ATTACH clause of the ALTER FRAGMENT statement.

For example, suppose that you have three nonfragmented tables, account1, account2, and account3, and that you store the tables in dbspaces dbspace1, dbspace2, and dbspace3, respectively. All three tables have identical structures, and you want to combine the three tables into one table that is fragmented by the expression on the common column acc_num.

You want rows with acc_num less than or equal to 1120 to be stored in dbspace1. Rows with acc_num greater than 1120 but less than or equal to 2000 are to be stored in dbspace2. Finally, rows with acc_num greater than 2000 are to be stored in dbspace3.

To fragment the tables with this fragmentation strategy, execute the following SQL statement:

ALTER FRAGMENT ON TABLE tab1 ATTACH
   tab1 AS acc_num <= 1120,
   tab2 AS acc_num >  1120 and acc_num <= 2000,
   tab3 AS acc_num > 2000;

The result is a single table, tab1. The other tables, tab2 and tab3, were consumed and no longer exist.

For information about how to use the ATTACH and DETACH clauses of the ALTER FRAGMENT statement to improve performance, see your IBM Informix: Performance Guide.

Using a Single Nonfragmented Table

To create a fragmented table from a nonfragmented table, use the INIT clause of the ALTER FRAGMENT statement. For example, suppose you want to convert the table orders to a table fragmented by round-robin. The following SQL statement performs the conversion:

ALTER FRAGMENT ON TABLE orders INIT 
   FRAGMENT BY ROUND ROBIN IN dbspace1, dbspace2, dbspace3;

Any existing indexes on the nonfragmented table become fragmented with the same fragmentation strategy as the table.