Using the reorg Command

Update activity against a table can eventually lead to inefficient utilization of space and reduced performance; use the reorg to command reorganize the use of table space, and improve performance.

reorg is useful when:

A large number of forwarded rows causes extra I/O during read operations.
Inserts and serializable reads are slow because they encounter pages with noncontiguous free space that must be reclaimed.
Large I/O operations are slow because of low cluster ratios for data and index pages.
sp_chgattribute has been used to change a space management setting (reservepagegap, fillfactor, or exp_row_size) and the change is to be applied to all existing rows and pages in a table, not just to future updates.

The reorg command includes four parameters for carrying out different types and levels of reorganization:

reorg forwarded_rows undoes row forwarding.
reorg reclaim_space reclaims unused space left on a page as a result of deletions and row-shortening updates.
reorg compact both reclaims space and undoes row forwarding.
reorg rebuild undoes row forwarding, reclaims unused page space, and:
- Rewrites all rows to accord with a table’s clustered index, if it has one
- Rewrites space for data and index partitions.
- Works on individual partitions.
- Writes rows to data pages to accord with any changes made in space management settings through sp_chgattribute
- Drops and re-creates all indexes belonging to the table

Consider the following before running reorg rebuild:

reorg rebuild holds an exclusive table lock for its entire duration. On a large table this may be a significant amount of time. However, reorg rebuild does everything that dropping and re-creating a clustered index does and takes less time. In addition, reorg rebuild rebuilds the table using all of the table’s current space management settings. Dropping and re-creating an index does not use the space management setting for reservepagegap.
In most cases, reorg rebuild requires additional disk space equal to the size of the table it is rebuilding and its indexes.

The following restrictions hold:

The table specified in the command, if any, must use the datarows locking or datapages locking scheme.
Versions of SAP ASE earlier than 15.0 restricted you from using reorg rebuild on allpages-locked tables. SAP ASE versions 15.0 and later allow you to run reorg rebuild on entire tables that uses allpages locking. reorg rebuild rebuilds the entire table, copying the data to new sets of pages, and rebuilding all indexes.
You must be a system administrator or the object owner to issue reorg.
You cannot issue reorg within a transaction.

reorg Command and Its Parameters
The reorg command includes a number of parameters.
Moving Forwarded Rows to Home Pages
If an update makes a row too long to fit on its current page, the row is forwarded to another page.
Reclaiming Unused Space from Deletions and Updates
When a task performs a delete operation, or an update that shortens row length, the empty space is preserved in case the transaction is rolled back; unreclaimed space may accumulate to the point that it impairs performance.
Reclaiming Unused Space and Undoing Row Forwarding
reorg compact combines the functions of reorg reclaim_space and reorg forwarded_rows.
Rebuilding a Table
Issue reorg rebuild to rewrite all rows in a table to new pages, so the table is arranged according to its clustered index (if one exists).
Using the reorg rebuild Command on Indexes
The reorg rebuild command allows you to rebuild indexes, while the table itself remains accessible for read and update activities.
resume and time Options for Reorganizing Large Tables
Use the resume and time options of the reorg command when reorganizing an entire table would take too long and interfere with other database activities.
Incremental Reorganization
The defrag parameter for the reorg command allows you schedule and resume reorganization, while also allowing concurrent reads or writes on the data being reorganized.