Contents


Flexible fragmentation strategy in Informix Dynamic Server 10.0

Consider the kind of application where data fragmentation is based on date expression, using a separate fragment for sales report for each day. Each day, the DBA needs to add a new dbspace for the date expression. If the application requires a large number of ranges in different fragments, the DBA is required to administer multiple dbspaces. Dbspace management, including dbspace creation and deletion, adds to the DBA's tasks.

The fragmentation strategy of IDS 9.4 (or earlier releases) does not allow tables to be fragmented with multiple fragments in one dbspace. Therefore, each dataset range needs a separate dbspace. The following example shows a customer table where fragment by expression is based on the STATE column. Each table fragment given below is associated with a separate dbspace. When you need to add a new state, you need to add a new dbspace.

Example 1
create table customer (id int, state char(2)) fragment by expression 
(state = "AZ") in dbspace1,
(state = "CA") in dbspace2,
(state = "WA") in dbspace3,
(state = "NY") in dbspace4;

By using multiple fragments in a single dbspace, the DBA can manage a large number of data fragments without looking for free space in the file system and a new name for the dbspace. DBA simply adds or attaches the new expression and specifies the new fragment name.

Fragmentation background

The IBM Informix database server supports table and index fragmentation (also called partitioning), allowing you to store a table on multiple disk devices. The proper fragmentation strategy significantly reduces I/O contention and increases 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. You specify the set of dbspaces in the IN clause (storage option) of these SQL statements.

A chunk is a contiguous section of disk space available for a database server. A dbspace includes one or more chunks. The DBA can add more chunks at any time, and should monitor dbspace usage and add chunks if necessary. A fragmentation strategy can be built on a table, an index, or both. A fragmentation distribution scheme can be either expression-based or round-robin-based. Expression-based fragmentation enables distributing the rows into multiple fragments based on a fragment expression (state = 'AZ' in the above example). Each fragment expression isolates a row 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. The round-robin fragmentation strategy distributes the rows so that the number of rows in each fragment remains approximately the same.

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 tblspace with a unique tblspace_id or fragment_id.

Old and new fragmentation strategies

IDS 9.4 supports two strategies: fragment by expression and fragment by round-robin. While this is good, IDS 9.4 requires that each table fragment is stored in a 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 causes manageability overhead requiring DBAs to create large number of dbspaces.

Limitations with IDS 9.4 (or earlier releases) fragment strategy:

  • For a given table or an index, it cannot store multiple fragments in a single dbspace. The DBA has to add a new dbspace for every new fragment expression.
  • The DBA has overhead to monitor a large number of dbspaces.
  • Limited chunks per dbspace.
  • Fixed page size for all dbspaces.

All of the above limitations are addressed in IDS 10.0 by using multiple fragments in a single dbspace, large chunk, and non-default page size support.

In IDS 10.0, the DBA is able to consolidate tables or indices on multiple dbspaces into a single dbspace. New tables and indices can be created with one or more fragments from one or more dbspaces. DBAs can manage a large number of tables or index fragments with a manageable number of dbspaces. In IDS 10.0, each fragment distribution schema is associated to a partition. Existing fragmentation strategy can easily be converted into a multiple fragment strategy using a partition syntax in the alter fragment command. A fragmented table or index can be created using old and new fragmentation syntax. Multiple fragment strategies do not impact the Parallel Database Query (PDQ) from its old behavior. Parallel threads are executed the same as old fragmentation strategy. Old fragmentation strategy is still supported in IDS 10.0.

In a new schema, a table uses a single dbspace but keeps the original fragment expression on the state column. Following az_part, ca_part, wa_part, and ny_part are partitions in the new fragmentation strategy.

Example 2
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;

Table and index creation

A DBA can create a new table or an index with multiple fragments in a single dbspace, as shown below. The partition keyword has been introduced to define fragment strategy.

Example 3. Create table case
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,
remainder in dbspace1;
Example 4. Create index case
create index  state_ind on customer (state ) fragment by expression 
partition az_part (state = "AZ") in dbspace2,
partition ca_part (state = "CA") in dbspace2,
partition wa_part (state = "WA") in dbspace2,
partition ny_part (state = "NY") in dbspace2,
remainder in dbspace2;

The round-robin fragment method can be applied to the customer table using a single dbspace fragment strategy.

Example 5. Round-robin case
create table customer ( id int, state char(2)) fragment by round robin
partition az_part in dbspace1,
partition ca_part in dbspace1,
partition wa_part in dbspace1,
partition ny_part in dbspace1;

A user can select the fragment expression to be stored in a single dbspace based on requirements. The following examples show how a user can combine old and new fragment strategies during table or index creation.

Example 6. Old and new fragmentation methods
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,
(state = "WA") in dbspace2,
(state = "NY") in dbspace3,
remainder in dbspace4;

create index  state_ind on customer (state) fragment by expression 
partition az_part (state = "AZ") in dbspace2,
partition ca_part (state = "CA") in dbspace2,
partition wa_part (state = "WA") in dbspace2,
(state = "NY") in dbspace3,
remainder in dbspace4;

The server also supports 'PARTITION BY EXPRESSION' instead of 'FRAGMENT BY EXPRESSION', and 'PARTITION BY ROUND ROBIN' instead of 'FRAGMENT BY ROUND ROBIN' in all statements with the single dbspace fragment strategy.

Example 7. 'PARTITION BY' cases
create table customer (id int, state char (2)) partition 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,
remainder in dbspace1;

create table customer ( id int, state char(2)) partition by round robin
partition az_part in dbspace1,
partition ca_part in dbspace1,
partition wa_part in dbspace1,
partition ny_part in dbspace1;

Alter fragment

A DBA can easily update existing fragmentation methods using the alter fragment command. These modification can be applied to both table and index fragmentation. A DBA can modify existing fragment strategies using alter fragment options. These options help to create mix fragment strategies based on requirements. The following example shows the usage of all table specific options, which are add, drop, attach, detach, modify, and init.

Example 8. Create table case
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,
remainder in dbspace2;
Example 9. Alter fragment cases for create table
alter fragment on table customer add partition part_or (state = "OR")
in dbspace1 before ca_part;

alter fragment on table customer drop partition part_or;
alter fragment on table customer attach customer_or as partition part_3 (state = "OR");
alter fragment on table customer detach partition part_3 customer_or;
alter fragment on table customer modify partition az_part to partition part_az 
(state = "AZ") in dbspace2;

alter fragment on table customer init fragment by expression 
partition az_part (state = "AZ") in dbspace2,
partition ca_part (state = "CA") in dbspace2,
partition wa_part (state = "WA") in dbspace3,
partition ny_part (state = "NY") in dbspace3,
remainder in dbspace3;
Example 10. Alter fragment cases for round robin
alter fragment on table customer add partition part_or in dbspace1;
alter fragment on table customer drop partition part_or;
alter fragment on table customer attach customer_or as partition part_3;
alter fragment on table customer detach partition part_3 customer_or;
alter fragment on table customer init fragment by round robin
partition az_part in dbspace2,
partition ca_part in dbspace2,
partition wa_part in dbspace3,
partition ny_part in dbspace3;
Example 11. Create index case
create index state_ind on customer (state) fragment by expression 
partition az_part state = "AZ" in dbspace2,
partition ca_part state = "CA" in dbspace2,
partition wa_part state = "WA" in dbspace2,
partition ny_part state = "NY" in dbspace2,
remainder in dbspace3;
Example 12. Alter fragment cases for create index
alter fragment on index state_ind add partition part_or (state = "OR")
in dbspace2 before ca_part;

alter fragment on index state_ind drop partition part_or;
alter fragment on index state_ind modify partition az_part to partition part_az 
(state = "AZ") in dbspace3;

alter fragment on index state_ind init fragment by expression 
partition az_part (state = "AZ") in dbspace2,
partition ca_part (state = "CA") in dbspace2,
partition wa_part (state = "WA") in dbspace3,
partition ny_part (state = "NY") in dbspace3,
remainder in dbspace3;

System catalog information

System catalog sysfragments has a partition column varchar(128,0), that provides a partition name for a given fragment. If you create a fragmented table with partitions, each row in the sysfragments system catalog contains a partition name in the Partition column. If you create a fragmented table without partitions, the name of the dbspace appears in the Partition column. The server continues to use the dbspace varchar(128,0) field for storing the dbspace name.

Example 13. Sysfragment catalog entry
select * from sysfragments where partition ="az_part";

fragtype    T
tabid       102
indexname   
colno       0
partn       2097159
strategy    E
location    L
servername  
evalpos     0
exprtext    
(state = 'AZ' )
exprbin    <BYTE value>
exprarr    <BYTE value>
flags       0
dbspace     dbspace1
levels      0
npused      0
nrows       0
clust       0
partition   az_part

Sqexplain output

Sqexplain output provides new syntax changes as shown below. All the fragment numbers in the output reference corresponding partitions from a single dbspace. Fragment 0 refers to the part_1 partition from dbspace1. Fragment expression for fragment 0 can be obtained by using the dbschema or sysfragments catalog. IDS does not change the query plan for PDQ environment as described in the Introduction section. Query plans in the PDQ environment have similar output along with secondary thread details.

Example 14. Create table case
create table t1 (c1 int ,c2 char(20)) fragment by expression
partition part_1 (c1 = 10) in dbspace1,
partition part_2 (c1 = 20) in dbspace1,
partition part_3 (c1 = 30) in dbspace1,
partition part_4 (c1 = 40) in dbspace1,
partition part_5 (c1 = 50) in dbspace1;
Example 15. Sqexplain cases
QUERY:
------
select count(*) from t1 where c1 = 10

Estimated Cost: 1
Estimated # of Rows Returned: 1

  1) informix.t1: SEQUENTIAL SCAN  (Serial, fragments: 0)

        Filters: informix.t1.c1 = 10

QUERY:
------
select * from t1 where c1 > 30

Estimated Cost: 2
Estimated # of Rows Returned: 26

  1) informix.t1: SEQUENTIAL SCAN  (Serial, fragments: 3, 4)

        Filters: informix.t1.c1 > 30

Database conversion

The DBA can use the new fragmentation strategy after database conversion is complete. During the conversion process, the dbspace column from sysfragments is saved to the partition column. The Alter Fragment table (or Index) converts the old fragment strategy to the new one on existing tables and index.

Example 16. Table schema on an old database before conversion process
create table customer (id int, state char (2)) fragment by expression 
 (state = "AZ") in dbspace1,
 (state = "CA") in dbspace2,
 (state = "WA") in dbspace3,
 (state = "NY") in dbspace4,
remainder in dbspace5;

Apply the alter fragment table statement to use new a fragment strategy after the database conversion. Both 'init' and 'modify' options alter the fragment strategy in this case.

Example 17. Alter fragment cases after conversion
alter fragment on table customer modify
dbspace1 to partition az_part (state = "AZ") in dbspace1,
dbspace2 to partition ca_part (state = "CA") in dbspace1,
dbspace3 to partition wa_part (state = "WA") in dbspace1,
dbspace4 to partition ny_part (state = "NY") in dbspace1,
dbspace5 to remainder in dbspace1;

alter fragment on table customer init 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,
remainder in dbspace1;
Example 18. Verify new fragment strategy from partition and exprtext columns of sysfragments
select partition, exprtext from sysfragments;

partition  az_part
exprtext   
(state = 'AZ' )

partition  ca_part
exprtext   
(state = 'CA' )

partition  wa_part
exprtext   
(state = 'WA' )

partition  ny_part
exprtext   
(state = 'NY' )

partition  dbspace1
exprtext   
remainder

5 row(s) retrieved.

Oncheck utility

Details about the new fragment method are provided in the oncheck utility. An additional partition name has been added to all of the following oncheck options. New oncheck output includes 'Table fragment partition <partition name> in DBspace <dbspace name>' format. When a new fragment method is not used, oncheck displays <dbspace name> for the table or index fragment partition. Oncheck output for the -cD, -cd, -cI , -ci, - pD , -pd , -pT, and -pt option show partition <partition name> along with the dbspace name.

Example 19. Oncheck output
oncheck -cD testdb:customer
        
   TBLspace data check for testdb:informix.customer
         Table fragment partition az_part in DBspace dbspace1
         Table fragment partition ca_part in DBspace dbspace1
         Table fragment partition wa_part in DBspace dbspace1
         Table fragment partition ny_part in DBspace dbspace1
         Table fragment partition dbspace1 in DBspace dbspace1


oncheck -cI testdb:customer

Validating indexes for testdb:informix.customer...
                Index state_ind
   Index  fragment partition az_part in DBspace dbspace2
         Index  fragment partition ca_part in DBspace dbspace2
         Index  fragment partition wa_part in DBspace dbspace2
         Index  fragment partition ny_part in DBspace dbspace2
         Index  fragment partition dbspace2 in DBspace dbspace2

Guidelines for fragmentation strategy

  • PDQ -- Delivers maximum performance benefits when the data that you query is in fragmented tables. Each decision-support query has a primary thread. The database server can start additional threads to perform tasks for the query (for example, scans and sorts). Depending on the number of tables or fragments that a query must search, and the resources that are available for a decision-support query, the database server assigns different components of a query to different threads.
  • Disk I/O -- DBAs need to decide the configuration of dbspaces based on available disk arrays. A resource is said to be critical to performance when it becomes overused or when its utilization is disproportionate. You can place a table with high I/O activity on a dedicated disk device, and thus reduce contention for the data that is stored in that table. When disk drives have different performance levels, you can put the tables with the highest use on the fastest drives. To isolate a highly used table on its own disk device, assign the device to a chunk, assign that chunk to a dbspace, and then place the table in the dbspace that you created.
  • Large Chunk -- IDS can hold a large number of partitions for the given dbspace by using large chunk support. Large chunk support can be used for increasing the number of data fragment expressions in a single dbspace. The size of chunks for dbspaces is 4 terabytes for a 2-kilobyte page. Chunks can reside anywhere in a 64-bit address space. The onmode -BC (backward-compatible) commands are useful if you have converted from IDS 9.40 (small chunk mode) to IDS 10.0. Onmode -BC 1 enables support of large chunks, large offsets that are greater than 2 GB, and allows more than 2047 chunks per dbspace. Onmode -BC 2 allows large-chunk-only mode for all dbspaces. When IDS 10.0 is first initialized (with the oninit -iyv command), by default it comes online with large chunk mode already fully enabled.
  • Non-default page size -- The root dbspace is the default page size 4KB on Windows and AIX, 2K on other platforms. The dbspace you create must use a multiple of the default page size, and cannot exceed 16 KB. You can specify a page size for a standard or temporary dbspace.

When you plan a fragmentation strategy, be aware of these space and page issues:

  • Although a 4-terabyte chunk can be on a 2-kilobyte page, only 32 gigabytes can be utilized in a dbspace because of a row ID format limitation.
  • For a fragmented table, all fragments must use the same page size.
  • For a fragmented index, all fragments must use the same page size.
  • Dbspaces used for table and indices on it can use different page size.

Downloadable resources


Related topics

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=169258
ArticleTitle=Flexible fragmentation strategy in Informix Dynamic Server 10.0
publish-date=10192006