Reorganizing Tables

Reorganize one or more tables to improve performance. By default, reorganization reorganizes all indexes within the selected table.

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 Reorganize.
3. On the Analysis screen, review space utilization to confirm that reorganization is needed.

   Space utilization uses the average row size and number of rows to compute the expected minimum number of data pages, and compares the expected minimum to the current number of pages. 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.

   If statistics have not been updated recently and the average row size has changed, or if the number of rows and pages are inaccurate, space utilization may report values greater than 1.0.

4. 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	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.

5. On the Options screen, select your reorganization options.

   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 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 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. 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.

6. Review the Summary page and click Finish.