Planning a Fragmentation Strategy

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 (or dbslices) in which you locate the fragments

You specify the set of dbspaces or dbslices 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 Designing a Distribution Scheme.
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 might must use the same page size.

For a fragmented index, all fragments might 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.

Enterprise Edition Home

Express Edition Home

Top of Page

Contents

Index