Planning a fragmentation strategy

You can decide on a fragmentation goal for your database and devise a strategy to meet that goal.

About this task

A 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 or in the IN clause (storage option) of these SQL statements.

To formulate a fragmentation strategy:

Decide on your primary fragmentation goal, which should depend, to a large extent, on the types of applications that access the table.
Make the following decisions based on your primary fragmentation goal:
- Whether to fragment the table data, the table index, or both
- What the ideal distribution of rows or index keys is for the table
Choose either an expression-based or round-robin distribution scheme:
- If you choose an expression-based distribution scheme, you must then design suitable fragment expressions.
- If you choose a round-robin distribution scheme, the database server determines which rows to put into a specific fragment.
For more information, see Distribution schemes.
To complete the fragmentation strategy, you must decide on the number and location of the fragments:
- The number of fragments depends on your primary fragmentation goal.
- Where you locate fragments depends on the number of disks available in your configuration.

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 rowid 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.
A table can be in one dbspace and the index for that table can be in another dbspace. These dbspaces do not need to have the same page size.