Reorganizing Table Partitions

Reorganize a table partition to improve performance.

Prerequisites

You must be a system administrator, or have reorg any table permissions when granular permissions is enabled.

Task
Reorganization is not available for all-pages-locked tables.
  1. In the left pane of the Administration Console, expand ASE Servers > Schema Objects > Tables, then choose one of the following:
    • User Tables
    • System Tables
    • Temporary Tables
    • Proxy Tables
  2. Click the Name field of the table, then click the drop-down arrow and select Properties.
  3. In the left pane, click Partitions.
  4. Click the Name field of the partition, then click the drop-down arrow and select Reorganize.
  5. On the commands screen, select the type and level of reorganization.
    Option Description Restrictions
    Compact Reclaim space and remove row forwarding. Not supported with all-pages-locked tables.
    Defragmentation Reorganize data and allow concurrent reads or writes. Not supported with:
    • Tables without an index (tables must have at least one index)
    • System tables
    • All-pages-locked tables
    Forwarded rows Remove row forwarding. Not supported with all-pages-locked tables.
    Rebuild Not supported with table partitions.
    Reclaim space Reclaim unused page space resulting from deletions and row-shortening updates. Not supported with all-pages-locked tables.
  6. On the Options screen, select your reorganization options.
    Option Description Restrictions
    Compress Compress the rows affected by the reorganization. Available only when one of the following is selected:
    • Compact
    • Forwarded rows
    • Reclaim space

    Not available on SAP ASE versions earlier than 15.7.

    Resume Start reorganization at the point in a table where the previous reorganization left off. Available only when one of the following is selected:
    • Compact
    • Forwarded rows
    • Reclaim space
    • Defragmentation, and resuming reorganization on a single table partition is feasible.
    Time Specify the length of time allowed for running reorganization.  
    Skip compact extents Specify the occupancy threshold of the extent. SAP ASE reorganizes only the extents for which compactness falls below the occupancy threshold; extents with a compactness higher than the threshold are not reorganized.

    The compactness of an extent is measured as the percentage range (1 – 100) occupancy in that extent (80 is the default).

    Compactness = (Total space occupied in an extent / Total space in an extent) x 100.

    Available only when Defragmentation is selected.
  7. Review the Summary page and click Finish.
Related concepts
Enabling Granular Permissions