Fragmentation goals

You can analyze your application and workload to identify fragmentation goals and to determine the balance to strike among fragmentation goals.

Fragmentation goals can include:
  • Improved performance for individual queries

    To improve the performance of individual queries, fragment tables appropriately and set resource-related parameters to specify system resource use (memory, CPU virtual processors, and so forth).

  • Reduced contention between queries and between transactions

    If your database server is used primarily for online transaction processing (OLTP) and only incidentally for decision-support queries, you can often use fragmentation to reduce contention when simultaneous queries against the same table perform index scans to return a few rows.

  • Increased data availability

    Careful fragmentation of dbspaces can improve data availability if devices fail. Table fragments on the failed device can be restored quickly, and other fragments are still accessible.

  • Improved data-load performance

    When you use the High-Performance Loader (HPL) to load a table that is fragmented across multiple disks, it allocates threads to insert the data into the fragments in parallel, using light appends. For more information about this load method, see the HCL OneDB™ High-Performance Loader User's Guide.

    You can use the ALTER FRAGMENT ON TABLE statement with the ATTACH clause to add data quickly to a very large table. For more information, see Improve the performance of operations that attach and detach fragments.

The performance of a fragmented table is primarily governed by the following factors: