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;

Get Informix Dynamic Server V10 . . . Extended Functionality for Modern Business now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.