Informix Dynamic Server V10 . . . Extended Functionality for Modern Business

Chapter 10. Really easy administration 283

Fragmentation Background

IDS V10 supports table and index fragmentation (also called partitioning) which

allows you to store a table on multiple disk devices. A proper fragmentation

strategy can significantly reduce the I/O contention and increase manageability.

The Informix fragmentation strategy consists of two parts:

 A distribution scheme that specifies how to group rows into fragments. You

specify the distribution scheme in the FRAGMENT BY clause of the CREATE

TABLE, CREATE INDEX, or ALTER FRAGMENT statements.

 The set of dbspaces in which you locate the fragments are specified by the IN

clause (storage option) of these SQL statements.

A fragmentation strategy can be built on a table, an index or both. Fragmentation

distribution schemes can be either expression based or round robin based:

 Expression based fragmentation enables distributing the rows into multiple

fragments based on a fragment expression (for example, state = “AZ”) as

shown in Example 10-1. Each fragment expression isolates rows and aids in

narrowing the search space for queries. You can define range rules or

arbitrary rules that indicate to the database server how rows are to be

distributed.

 Round-robin fragmentation strategy distributes the rows so the number of

rows in each fragment remains approximately the same.

For a common understanding, here are a few definitions of terms used:

 A table fragment (partition) refers to zero or more rows that are grouped

together and stored in a dbspace that you specify when you create the

fragment. Each table fragment has its own tablespace with a unique

tblspace_id or fragment_id.

 A dbspace includes one or more chunks. You typically monitor the dbspace

usage and add chunks as necessary.

 A chunk is a contiguous section of disk space available for a database server.

10.1.2 Fragmentation strategies

Pre IDS V10 releases, for example IDS V9.4, supported two strategies, fragment

by expression and fragment by round robin. Although this is good, it requires

each table fragment to be stored in distinct dbspaces. One dbspace can contain

fragments from multiple tables, but a single table cannot have more than one

fragment in a single dbspace. This can result in manageability overhead,

requiring the creation and monitoring of a large number of dbspaces. In addition,

there is a limitation on the number of chunks per dbspace, and a fixed page size

for all dbspaces.

284 Informix Dynamic Server V10 . . . Extended Functionality for Modern Business

All of the above mentioned limitations are addressed in IDS V10 by using

multiple fragments in a single dbspace, large chunk and non-default page size

support.

In IDS V10, you can consolidate tables or indexes on multiple dbspaces into a

single dbspace. New tables and indexes can be created with one or more

fragments from one or more dbspaces. And, you can manage a large number of

table or index fragments with a manageable number of dbspaces.

Also in IDS V10, each fragment distribution scheme is associated with a partition.

So the existing fragmentation strategy can easily be converted into a multiple

fragment strategy by using partition syntax in the alter fragment command.

Fragmented tables or indexes can be created using old and new fragmentation

syntax. And, the multiple fragment strategy does not impact PDQ. Parallel

threads are executed the same as with the old fragmentation strategy, and the

old fragmentation strategy is still supported in IDS V10.

New fragment strategy

In the new schema, as shown in Example 10-2, the table uses a single dbspace

but keeps original fragment expression on state column. The partitions in the new

fragmentation strategy are az_part, ca_part, wa_part and ny_part.

Example 10-2 Expression based fragments in a single dbspace

CREATE TABLE customer

(

id INT,

state CHAR(2)

)

FRAGMENT BY EXPRESSION

PARTITION az_part (state = "AZ") IN dbspace1,

PARTITION ca_part (state = "CA") IN dbspace1,

PARTITION wa_part (state = "WA") IN dbspace1,

PARTITION ny_part (state = "NY") IN dbspace1,

PARTITION remainder_part REMAINDER IN dbspace1;