Planning a fragmentation strategy

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

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:

  1. Decide on your primary fragmentation goal, which should depend, to a large extent, on the types of applications that access the table.
  2. 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
  3. 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.

  4. 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.

Copyright© 2020 HCL Technologies Limited