reorg

Reclaims unused space on pages, removes row forwarding, or rewrites all rows in the table to new pages, depending on the option used.

Syntax

reorg compact table_name [partition partition_name]
	[with {resume, time = no_of_minutes, compress}]

reorg forwarded_rows table_name [partition partition_name] 
	[with {resume, time = no_of_minutes, compress}]

reorg rebuild table_name [index_name [partition index_partition_name
	[with online]]]

reorg reclaim_space table_name [index_name] [partition partition_name]
	[with {resume, time = no_of_minutes, compress}]

reorg defrag table_name [partition {partition_list}]
   [with {time = hh:mm| resume | skip_compact_extents [= pct_value]}]

Parameters

compact – combines the functions of reorg reclaim_space and reorg forwarded_rows to both reclaim space and undo row forwarding in the same pass.
forwarded_rows – removes row forwarding.
While unforwarding or reinserting a data row on a table configured for compression, the row is compressed according to the table’s compression level.
index_partition_name – is the name of the index partition on which you are running reorg. update statistics peforms a check to validate that index_partition_name is an index partition. If you specify an index partition, only that index partition is rebuilt
indexname – specifies the name of the index to be reorganized.
partition_name – is the name of the partition on which you are running reorg.
tablename – specifies the name of the table to be reorganized. If indexname is specified, only the index is reorganized.
rebuild – if a table name is specified, rewrites all rows in a table to new pages, so that the table is arranged according to its clustered index (if one exists), with all pages conforming to current space management settings and with no forwarded rows and no gaps between rows on a page. If the table has an index, all indexes are dropped and re-created. If an index name is specified, reorg rebuilds that index while leaving the table accessible for read and update activities.
New rows follow the compression level of the partition or table, regardless of the compression level of the data in the original table or partition.

Note: reorg rebuild is not supported for system catalogs.
with online – allows concurrent access to the table while reorg rebuild runs.
reclaim_space – reclaims unused space left by deletes and updates. For each data page in a table, if there is unused space resulting from committed deletes or row-shortening updates, reorg reclaim_space rewrites the current rows contiguously, leaving all unused space at the end of the page. If there are no rows on the page, the page is deallocated.
If a table is marked for compression, reclaim_space compresses the data.

Note: reorg reclaim_space only affects tables with variable-length rows, and only frees up space within a page. To reduce the number of pages used, use the reorg rebuild command.
with resume – initiates reorganization from the point at which a previous reorg command terminated. Used when the previous reorg command specified a time limit (with time = no_of_minutes).
with time = no_of_minutes – specifies the number of minutes that the reorg command is to run.
with compress – allows you to compress the rows affected by the reorg operation.
defrag – reorganizes each partition list or partition in the table while allowing concurrent reads or writes on the data being reorganized.
partition – is the subset of the table that shares the same column definitions, constraints, and defaults as the table.
partition_list – is the list of partition names.
time – reorganizes the table or the list of partitions for the specified interval of time. hh is the number of hours and has no limit, and mm is the number of minutes 0–59.
resume – resumes table reorganization from the end of the last reorganized data page invoked from the previous reorg defrag. resume continues until the entire table or list of partitions is reorganized, processing only those partitions that are currently either unvisited or partially reorganized. Running time = hh:mm with resume indicates that you are running reorganization from the previous position of reorganization and running it only for the specified interval of time.
skip_compact_extents – skips compact extents. The compactness of an extent is measured as the percentage occupancy in that extent.
pct_value – is the compactness of an extent measured as the percentage occupancy in that extent with a value of 1–100.
Compactness = (Total space occupied in an extent / Total space in an extent) x 100

If skip_compact_extents is used, all the extents with compactness greater than or equal to the threshold occupancy percent value specified would be skipped for reorganization. If no threshold percent value is specified, the default percent value is 80%.

Examples

Example 1 – Reclaims unused page space in the titles table:
```
reorg reclaim_space titles
```
Example 2 – Reclaims unused page space in the index titleind:
```
reorg reclaim_space titles titleind
```
Example 3 – Initiates reorg compact on the titles table. reorg starts at the beginning of the table and continues for 120 minutes. If the reorg completes within the time limit, it returns to the beginning of the table and continues until the full time period has elapsed:
```
reorg compact titles with time = 120
```
Example 4 – Initiates reorg compact at the point where the previous reorg compact stopped and continues for 30 minutes:
```
reorg compact titles with resume, time = 30
```
Example 5 – Runs reorg forwarded_rows on the smallsales partition of the titles table:
```
reorg forwarded_rows titles partition smallsales
```
Example 6 – Runs reorg forwarded_rows on the authors table:
```
reorg forwarded_rows authors
```
Example 7 – Runs reorg reclaim_space on the bigsales partition of titles:
```
reorg reclaim_space titles partition bigsales
```
Example 8 – Runs reorg compact on the bigsales partition of titles:
```
reorg compact titles partition bigsales
```
Example 9 – Runs reorg compact on the titles table and compresses the affected rows:
```
reorg compact titles with compress
```
Example 10 – Runs reorg rebuild on the index partition idx_p2 of index local_idx on table sales:
```
reorg rebuild sales local_idx partition idx_p2
```
Example 11 – Reorganizes the partition list, starting from the beginning of each partition and continuing until its end:
```
reorg defrag salesdetail [partition {seg1 [,seg2[, seg3]]}]
```
Example 12 – Reorganizes the partition list, starting from the beginning of each partition and continuing until the specified time interval is reached.
```
reorg defrag salesdetail [partition 
   {seg1 [,seg2[,seg3]]}] with time = 01:20 
```
Example 13 – Restarts reorganization for each partition in the partition list that is either unvisited or partially reorganized, from where the previous invocation of reorganization has stopped, and if no point for resuming is available, reorganization starts from the beginning of the partition. Reorganization continues until the end of each partition.
```
reorg defrag salesdetail [partition {seg1 [,seg2[,seg3]]}] 
   with resume
```
Example 14 – Reorganizes the partition list, starting from the beginning of the partition and continuing until its end, skipping the extents with more than the specified occupancy threshold. If a percentage is not specified, extents exceeding 80% occupied are considered compact, and skipped.
```
reorg defrag salesdetail [partition {seg1 [,seg2[,seg3]]}] 
   with skip_compact_extents [ = <1-100>]
```
Example 15 – Reorganizes each partition in the partition list that is either unvisited or partially reorganized, starting from the point where the previous invocation of reorganization stopped. If no point is available for resuming, reorganization starts from the beginning of the partition. Reorganization continues until the specified time interval is reached.
```
reorg defrag salesdetail [partition {seg1 [,seg2[,seg3]]}] 
   with time = 01:20, resume
```
Example 16 – Reorganizes the partition list, starting from the beginning of the partition and continuing until the specified interval of time completes, skipping each extent that qualifies to be compact as per the pct_value specified for skip_compact_extents. If a percentage is not specified, extents exceeding 80% occupied are considered compact, and skipped.
```
reorg defrag salesdetail [partition {seg1 [,seg2[,seg3]]}] 
   with time = 01:20, skip_compact_extents [ = <1-100>]
```
Example 17 – Reorganizes each partition in the partition list that is either unvisited or partially reorganized, starting from the point where the previous invocation of reorganization stopped. If no point is available for resuming, reorganization starts from the beginning of the partition. Reorganization continues until the end of each partition. It skips extents that qualify to be compact as per the pct_value provided for skip_compact_extents. If the pct_value is not provided, extents exceeding 80% occupied are considered compact and hence skipped.
```
reorg defrag salesdetail [partition {seg1 [,seg2[,seg3]]}] 
   with resume, skip_compact_extents[ = <1-100>] 
```
Example 18 – Reorganizes each partition in the partition list that is either unvisited or partially reorganized, starting from the point where the previous invocation of reorganization has stopped. If no point is available for resuming, reorganization starts from the beginning of the partition. Reorganization continues until the specified time interval completes. It skips extents that qualify to be compact as per the pct_value specified for skip_compact_extents. If a percentage is not specified, extents exceeding 80% occupied are considered compact and hence skipped.
```
reorg defrag salesdetail [partition {seg1 [,seg2[,seg3]]}] 
   with time = 01:20, resume, skip_compact_extents [ = <1-100>]
```
For every 10% of a partition processed, this information appears:
```
Examined n allocation unit(s). Processed x pages out of y 
data pages. z% completed, resulting in p% space compaction.
```
At the end of processing each partition, the time elapsed in the current invocation is printed as:
```
Elapsed time 1m : 56s : 623ms.
```

Usage

You cannot run reorg rebuild on system tables.
The table specified in reorg—excluding reorg rebuild—must have a datarows- or datapages-locking scheme.
Index scans traverse faster after you run reorg.
Running reorg against a table can have a negative effect on performance of concurrent queries.
If you do not include the index or partition name, the entire table is rebuilt.
You can perform a dump tran on a table after rebuilding its index. However, you cannot perform a dump tran if the entire table has been rebuilt.
Although online index rebuilding is allowed on a placement index, it rebuilds only the index pages. The data pages remain untouched, which means datarows are neither sorted nor rewritten to fresh pages. You can rebuild data pages by dropping a placement index, and then re-creating it.
You can rebuild the index for systabstats, but you cannot run reorg rebuild on the table itself.
Versions of SAP ASE earlier than 15.0 restricted you from using reorg rebuild on all-pages 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 rebuilds all indexes.
You cannot use the reorg rebuild sub commands (for example, compact, reclaim_space, and forwarded_rows) on all-pages-locked tables.
You cannot use reorg rebuild table_name index_name or partition_name on allpages-locked tables.
Running reorg rebuild table_name updates the statistics for all leading index columns. However, running reorg rebuild table_name index_name does not automatically update the statistics. Instead, the SAP ASE server automatically updates index statistics when you run reorg rebuild index_name if the update includes a suffiecient change in data to affect its plan choice and performance.
You can run writetext concurrently with the online parameter.
reorg has no effect on space allocated to text or image columns.
You cannot issue reorg within a transaction.
reorg rebuild requires that you set the database option select into/bulkcopy/pllsort to true and run checkpoint in the database.
reorg rebuild requires additional disk space equal to the size of the table and its indexes. You can find out how much space a table currently occupies by using sp_spaceused. You can use sp_helpsegment to check the amount of space available.
After running reorg rebuild, you must dump the database before you can dump the transaction log.
Requirements for using reorg rebuild on an index are less stringent than for tables. The following rules apply:
- You do not need to set select into to rebuild an index.
- Rebuilding a table requires space for a complete copy of the table. Rebuilding an index works in small transactions, and deallocates pages once they are copied; therefore, the process needs space only for the pages copied on each transaction.
- You can rebuild the index on a table while transaction level scans (dirty reads) are active.

When using reclaim_space, forwarded_rows, and compact parameters:

Minimize interference with other activities by using multiple small transactions of brief duration. Each transaction is limited to eight pages of reorg processing.
Rewrite space for a single partition.
Provide resume and time options that allow you to set a time limit on how long a reorg runs and to resume a reorg from the point at which the previous reorg stopped. This allows you to, for example, use a series of partial reorganizations at off-peak times to run the reorg command on a large table.

Garbage collection and locks:

For datarow tables – the SAP ASE server performs garbage collection using a latch, and releases the latch on a page before moving to the next page. No locks are obtained until the garbage collection encounters a forwarded row, when it acquires an exclusive table lock, which it holds until the end of that transaction. Subsequent transaction use latches until they encounter forwarded rows.
For data page tables – the SAP ASE server performs garbage collection using a page lock, but releases the page lock before moving to the next page. When the gargage collector encounters a forwarded row, it acquires an exclusive table lock, which it holds until the end of the transaction. Subsequent transactions use page locks until it encounters another forwarded row.
Use reorg compact if garbage collection encounters OAM pages that are allocated to the object, but do not refer to the allocation (running reorg compact requires a shared table lock).

The following considerations apply when using the resume and time parameters:

If you specify only the resume option, the reorg begins at the point where the previous reorg stopped and continues to the end of the table.
If you specify only the time option, the reorg starts at the beginning of the table and continues for the specified number of minutes.
If you specify both options, the reorg starts at the point where the previous reorg stopped and continues for the specified number of minutes.

When running reorg on compressed tables:

reorg rebuild – sorts the rows according to the placement index (if one exists), writing the rows to new data pages according to the space management settings currently in effect. New rows are compressed or decompressed according to individual partition’s compression level, regardless of the data compression state in the original table or partition.
reorg reclaim_space – compresses data rows to save more space if the table is marked for compression.

The following considerations apply when using the reorg defrag:

Tables must be data-only-locking (DOL) and have at least one index to use reorg defrag. Sybase recommends that the table has a unique index to efficiently accommodate the movement of the forwarded locations during reorganization.
You can run only one instance of reorg defrag at a time on a table.
When reorg defrag is in progress, DDLs cannot run on the table, for example add/drop partition, create/drop index, add/modify/drop columns. Also, other existing reorg subcommands like rebuild/reclaim/compact/forwarded rows cannot run when reorg defrag is in progress.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

The permission checks for reorg differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must be the table owner, or a user with `reorg any table` privilege.
Disabled	With granular permissions disabled, you must be the table owner or a user with sa_role.