Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - SQL Reference » SQL statements » Alphabetical list of SQL statements


Defragments tables when a full rebuild of the database is not possible due to the requirements for continuous access to the database.

REORGANIZE TABLE [ owner.]table-name
| FOREIGN KEY foreign-key-name
| INDEX index-name } ]

Reorganize the table according to the values in one of the following:

  • PRIMARY KEY clause

    Reorganizes the primary key index for the table.

  • FOREIGN KEY clause

    Reorganizes the specified foreign key.

  • INDEX clause

    Reorganizes the specified index.


Table fragmentation can impede performance. Use this statement to defragment rows in a table, or to compress indexes which have become sparse due to DELETEs. It may also reduce the total number of pages used to store the table and its indexes, and it may reduce the number of levels in an index tree. However, it will not result in a reduction of the total size of the database file. Use the sa_table_fragmentation and sa_index_density system procedures to select tables worth processing.

If an index or key is not specified, the reorganization process defragments rows in the table by deleting and re-inserting groups of rows. For each group, an exclusive lock on the table is obtained. Once the group has been processed, the lock is released and re-acquired (waiting if necessary), providing an opportunity for other connections to access the table. Checkpoints are suspended while a group is being processed; once a group is finished, a checkpoint may occur. The rows are processed in order of the clustered index if one exists; otherwise, they are processed in order of the primary key. If the table does not have a clustered index or a primary key, an error is returned. The processed rows are re-inserted at the end of the table, resulting in the rows being clustered by primary key at the end of the process. The same amount of work is required, regardless of how fragmented the rows initially were.

If an index or key is specified, the specified index is processed. For the duration of the operation, an exclusive lock is held on the table and checkpoints are suspended. Any attempts to access the table by other connections will block or fail, depending on their setting of the blocking option. The duration of the lock is minimized by pre-reading the index pages before obtaining the exclusive lock.

Since reorganization may modify many pages, the checkpoint log can become large. This can result in an increase in the database file size. However, this increase is temporary since the checkpoint log is deleted at shutdown and the file is truncated at that point.

This statement is not logged to the transaction log.

This statement cannot be executed when there are cursors opened with the WITH HOLD clause that use either statement or transaction snapshots.

During the execution of this statement, you can request progress messages.

You can also use the Progress connection property to determine how much of the statement has been executed.


You must be the owner of the table, or have the REORGANIZE ANY OBJECT system privilege.

Side effects

Before starting the reorganization, a checkpoint is done to try to maximize the number of free pages. Also, when executing the REORGANIZE TABLE statement, there is an implied commit for approximately every 100 rows, so reorganizing a large table causes multiple commits to take place.

  • ANSI/ISO SQL Standard

    Not in the standard.


The following statement reorganizes the primary key index for the Employees table:


The following statement reorganizes the table pages of the Employees table:


The following statement reorganizes the index IX_product_name on the Products table:

   INDEX IX_product_name;

The following statement reorganizes the foreign key FK_DepartmentID_DepartmentID for the Employees table:

   FOREIGN KEY FK_DepartmentID_DepartmentID;