Informix Guide to SQL: Tutorial

Informix Guide to SQL: Tutorial
Chapter 9: Implementing Your Data Model

Home Contents Index Master Index New Book

Modifying a Fragmented Table

You can make two general types of modifications to a fragmented table. The first type consists of the modifications that you can make to a nonfragmented table. Such modifications include adding a column dropping a column, changing a column data type, and so on. For these modifications, use the same SQL statements that you would normally use on a nonfragmented table.

The second type of modification consists of changes to a fragmentation strategy. This section explains how to modify a fragmentation strategy using SQL statements.

Modifying Fragmentation Strategies

The need to alter a fragmentation strategy after you implement fragmentation sometimes occurs. Most frequently, you will need to modify your fragmentation strategy when you use fragmentation with intraquery parallelization or interquery parallelization. Modifying your fragmentation strategy in these circumstances is one of several ways you can tune the performance of your Universal Server system.

Using the MODIFY Clause to Change a Fragmentation Strategy

To modify an existing fragmentation strategy, use the ALTER FRAGMENT statement. Use the MODIFY clause of the ALTER FRAGMENT statement to modify one or more of the expressions in a fragmentation strategy.

For example, suppose that you initially created the fragmented table with the following CREATE TABLE statement:

CREATE TABLE account (acc_num INTEGER, ............)

	FRAGMENT BY EXPRESSION

		acc_num <= 1120 in dbspace1,

		acc_num > 1120 and acc_num < 2000 in dbspace2,

		REMAINDER IN dbspace3

Executing the following ALTER FRAGMENT statement ensures that no account numbers with a value less than or equal to zero are stored in the fragment that is contained in dbspace1:

ALTER FRAGMENT ON TABLE account

	MODIFY dbspace1 to acc_num > 0 and acc_num <=1120

You cannot use the MODIFY clause to alter the number of fragments contained in your distribution scheme. Use the INIT or ADD clause of ALTER FRAGMENT described in the next section instead.

Adding a New Fragment

If the modifications that you want to make require adding a new fragment to your table, use the ADD clause of the ALTER FRAGMENT statement.

For example, suppose that you want to add a fragment to a table that you created using the following SQL statement:

CREATE TABLE frag_table ... 

	FRAGMENT BY ROUND ROBIN IN dbspace1,dbspace2,dbspace3

To add a fourth dbspace, dbspace4, execute the following SQL statement:

ALTER FRAGMENT ON TABLE frag_table ADD dbspace4

The ADD clause of ALTER FRAGMENT contains options for adding a dbspace before or after an existing dbspace, provided the fragmentation strategy is expression based. For more information, see the ALTER FRAGMENT statement in the Informix Guide to SQL: Syntax.

Using the INIT Clause to Reinitialize a Fragmentation Scheme Completely

Consider using the INIT clause when you want to reinitialize a fragmentation strategy completely. For example, suppose that you initially created the fragmented table with the following CREATE TABLE statement:

CREATE TABLE account (acc_num INTEGER, ............)

	FRAGMENT BY EXPRESSION

		acc_num <= 1120 in dbspace1,

		acc_num > 1120 and acc_num < 2000 in dbspace2,

		REMAINDER IN dbspace3

However, after several months of operation with this distribution scheme, you find that the number of rows in the fragment contained in dbspace2 is twice the number of rows contained in the other two fragments. This imbalance causes the disk containing dbspace2 to become an I/O bottleneck.

To remedy this situation, you decide to modify the distribution so that the number of rows in each fragment is approximately even. You want to modify the distribution scheme so that it contains four fragments instead of three fragments. A new dbspace, dbspace2a, is to contain the new fragment that will store the first half of the rows that were previously contained in dbspace2. The fragment in dbspace2 will contain the second half of the rows that it previously stored.

To implement the new distribution scheme, first create the dbspace
dbspace2a. Then execute the following statement:

ALTER FRAGMENT ON TABLE account INIT 

	FRAGMENT BY EXPRESSION

		acc_num <= 1120 in dbspace1,

		acc_num >   1120 and acc_num <= 1500 in dbspace2a,

		acc_num >   1500 and acc_num < 2000 in dbspace2,

		REMAINDER IN dbspace3

As soon as you execute this statement, Universal Server discards the old fragmentation strategy, and the rows contained in the table are redistributed according to the new fragmentation strategy.

You can also use the INIT clause of ALTER FRAGMENT to perform the following actions:

Convert a single nonfragmented table into a fragmented table
Convert a fragmented table into a nonfragmented table
Convert a table fragmented by round-robin to an expression-based fragmentation strategy
Convert a table fragmented by expression to a round-robin fragmentation strategy

For more information, see the ALTER FRAGMENT statement in the Informix Guide to SQL: Syntax.

Dropping a Fragment

In the process of defining a fragmentation strategy, you might find it necessary to drop one or more fragments. Suppose you wish to drop a fragment that was defined by this SQL statement:

CREATE TABLE frag_table (col_a int, col_b int) 

	FRAGMENT BY ROUND ROBIN IN dbspace1,dbspace2,dbspace3

To drop the second fragment, issue the following SQL statement:

ALTER FRAGMENT ON TABLE frag_table DROP dbspace2

When you issue this statement, all the rows in dbspace2 are moved to the remaining dbspaces, dbspace1 and dbspace3. For more information on dropping fragments, see the ALTER FRAGMENT statement in Chapter 1 of the Informix Guide to SQL: Syntax.

Informix Guide to SQL: TutorialChapter 9: Implementing Your Data Model Home Contents Index Master Index New Book

Modifying a Fragmented Table

Modifying Fragmentation Strategies

Using the MODIFY Clause to Change a Fragmentation Strategy

Adding a New Fragment

Using the INIT Clause to Reinitialize a Fragmentation Scheme Completely

Dropping a Fragment

Informix Guide to SQL: Tutorial
Chapter 9: Implementing Your Data Model

Home Contents Index Master Index New Book