Informix Guide to SQL: Tutorial

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

Home Contents Index Master Index New Book

Creating a Fragmented Table

You can fragment a table at the same time that you create it, or you can fragment existing nonfragmented tables. An overview of both alternatives is given in the following sections. For the complete syntax of the SQL statements that you use to create fragmented tables, see the CREATE TABLE and ALTER TABLE statements in the Informix Guide to SQL: Syntax.

Before you create a fragmented table, you must decide on an appropriate distribution scheme for your tables. For advice on choosing a distribution scheme that meets your needs, see the INFORMIX-Universal Server Administrator's Guide.

Fragmenting a New Table

To create a fragmented table, use the FRAGMENT BY clause of the CREATE TABLE statement. Suppose that you wish to create a fragmented table similar to the stores7 table, orders. You decide on a round-robin distribution scheme with three fragments. Consult with the Universal Server administrator to set up three dbspaces, one for each of the fragments: dbspace1, dbspace2, and dbspace3. To create the fragmented table, execute the following SQL statement:

CREATE TABLE my_orders (

	order_num SERIAL(1001),

	order_date DATE,

	customer_num INT, 

	ship_instruct CHAR(40),

	backlog CHAR(1),

	po_num CHAR(10),

	ship_date DATE,

	ship_weight DECIMAL(8,2),

	ship_charge MONEY(6),

	paid_date DATE,

	PRIMARY KEY (order_num),

	FOREIGN KEY (customer_num) REFERENCES customer(customer_num))

	FRAGMENT BY ROUND ROBIN IN dbspace1,dbspace2,dbspace3

If you decide instead to create the table using an expression-based distribution scheme, you can use the FRAGMENT BY EXPRESSION clause of CREATE TABLE. Suppose that your my_orders table has 30,000 rows, and you wish to distribute rows evenly across three fragments stored in dbspace1, dbspace2, and dbspace3. You decide to use the column order_num to define the expression fragments.

You can define the expression as the following example shows:

CREATE TABLE my_orders (order_num serial, ...)

	FRAGMENT BY EXPRESSION

		    order_num <   10000 IN dbspace1,

		    order_num <   20000 IN dbspace2,

		    order_num >= 20000 IN dbspace3

For information about how you can specify a fragmentation strategy for typed tables that are part of an inheritance hierarchy, see "Inheritance of Table Behavior in a Table Hierarchy".

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.

In this case, 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.

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

Follow the instructions in the section

"Creating a Fragmented Table from a Single Nonfragmented Table"

Tip: Before you perform the conversion, you must set up an appropriate number of dbspaces to contain the newly created fragmented tables.

Creating a Table from 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 the 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 the 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 expression on the common column acc_num.

You want rows with acc_num less than or equal to 1120 to be stored in the fragment that is 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, tabl. The other tables, tab2 and tab3, were consumed and no longer exist. For more information on the ATTACH clause of the ALTER FRAGMENT statement, see Chapter 1 of the Informix Guide to SQL: Syntax.

Creating a Fragmented Table from 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

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

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

Creating a Fragmented Table

Fragmenting a New Table

Creating a Fragmented Table from Nonfragmented Tables

Creating a Table from More Than One Nonfragmented Table

Creating a Fragmented Table from a Single Nonfragmented Table

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

Home Contents Index Master Index New Book