Reorganizing Indexes

Reorganize an index 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 Indexes.
  4. Click the Name field of the index, then click the drop-down arrow and select Reorganize.
  5. On the Analysis screen, review the space utilization information to confirm that reorganization is needed.

    If space utilization is low, run reorganization.

    See Performance and Tuning Series: Improving Performance with Statistical Analysis.

    Space utilization is 0 when row count is equal to 0. For derived statistics on space utilization, the row count is based on the information from systabstats.

  6. On the Commands screen, select the type and level of reorganization.
    Option Description Restrictions
    Compact Not supported with indexes.
    Defragmentation Not supported with indexes.
    Forwarded rows Not supported with indexes.
    Rebuild
    • Remove row forwarding and reclaim unused page space.
    • Rewrite all rows to accord with a clustered index for a table, if it has one.
    • Write rows to data pages to accord with any changes made in space management settings through sp_chgattribute.
    • Drop and re-create all indexes belonging to the table.

    Requires that the select into database option is set to true.

    Not supported with system tables.

    Rebuilding an index on an all-pages-locked table is not supported.

    Reclaim space Reclaim unused page space resulting from deletions and row-shortening updates.  
  7. If you selected Reclaim space in the previous screen, select your reorganization options:
    • Compress – compress the rows affected by the reorganization.
    • Resume – start reorganization at the point in a table where the previous reorganization left off.
    • Time – specify the length of time allowed for running reorganization.
  8. Review the Summary page and click Finish.
Related concepts
Enabling Granular Permissions