Partition defragmentation

You can improve performance by defragmenting partitions to merge non-contiguous extents.

A frequently updated table can become fragmented over time, which degrades performance every time the table is accessed by the server. Defragmenting a table brings data rows closer together and avoids partition header page overflow problems. Defragmenting an index brings the entries closer together, which improves the speed at which the table information is accessed.

Before you defragment a table, index, or partition, be sure that none of the following conflicting operations are in progress:

• An existing defragment operation on the table, index, or dbspace.
• DDL statements, such as DROP TABLE or ALTER FRAGMENT, are being run on the table or partition.
• The table is being truncated.
• The table is being compressed or uncompressed.
• An online index build is running.

You cannot defragment the following objects:

• Pseudo tables, such as virtual-table interface (VTI) tables
• Tables with virtual-index interface (VII) indexes
• Tables with functional indexes
• Temporary tables
• Sort files
• A table that has exclusive access set

To determine how many extents a table, index, or partition has, you can run the oncheck -pt command.

To defragment a table, index, or partition, run the SQL administration API task() or admin() function with the defragment argument or the defragment partnum argument and specify the table name, index, or partition number that you want to defragment.