reorg

Description

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}]

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.

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

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

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

Usage


The reclaim_space, forwarded_rows, and compact parameters


Garbage collection and locks


Using the resume and time parameters

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


Running reorg on compressed tables

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

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

Granular permissions enabled

With granular permissions enabled, you must be the table owner, or a user with reorg any table privilege.

Granular permissions disabled

With granular permissions disabled, you must be the table owner or a user with sa_role.

See also

Documents See the System Administration Guide.

System procedures sp_chgattribute