Reorganizing Tables at the Database Level

Reorganize a table at the database level to improve performance.

Prerequisites
You must be a system administrator, or have "reorg any table" permissions when granular permissions is enabled.
Task
You can reorganize only one database at a time.
  1. In the Perspective Resources view, select the server on which the table resides, then click the drop-down arrow next to the server name and select Administration Console.
  2. In the left pane of the Administration Console, expand ASE Servers > Schema Objects > Databases > database name and click the Name field of the database.
  3. Click the arrow and select Reorganize Tables.
  4. Click Next.
  5. Choose all tables, exclude system tables, or search for a table and click Next.
    • To reorganize all tables in the database, click Select all tables.
    • To search for a table by name, click Use search criteria, select Name contains, enter the table name, and click Search. Select one or more tables from the list.
    • To search for a table by space utilization, click Use search criteria, select Space utilization <=, enter the value, and click Search. Select one or more tables from the list.
    • Choose whether to select Exclude system tables.
  6. Select the type and level of reorganization, then click Next.
    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
    • 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 to be true.

    Not supported with system tables.
    Reclaim space Reclaim unused page space resulting from deletions and row-shortening updates. Not supported with all-pages-locked tables.
  7. Select your reorganization options, then click Next.
    Option Description Restrictions
    Compress Compress the rows affected by the reorganization. Available only when one of these reorganization types is selected:
    • Compact
    • Forwarded rows
    • Reclaim space

    Not available on Adaptive Server 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 these reorganization types is selected:
    • Compact
    • Forwarded rows
    • Reclaim space
    • Defragmentation, and resuming reorganization on a single table is feasible.
    Time Specify the length of time allowed for running reorganization. Available only when one of these reorganization types is selected:
    • Compact
    • Forwarded rows
    • Reclaim space
    • Defragmentation
    Skip compact extents Specify the occupancy threshold of the extent. Adaptive Server 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.
  8. Review the Summary page and click Finish.
Parent topic: Manage Reorganization
Related concepts
Enabling Granular Permissions