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

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » SQL Statements

REMOVE JAVA statement Next Page


Use this statement to defragment 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 }


PRIMARY KEY    Reorganizes the primary key index for the table.

FOREIGN KEY    Reorganizes the specified foreign key.

INDEX    Reorganizes the specified index.

ORDER option    With ORDER ON (the default), the data is ordered by clustered index if one exists. If a clustered index does not exist, the data is ordered by primary key values. With ORDER OFF, the data is ordered by primary key.

For more information about clustered indexes, see Using clustered indexes.


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. It is recommended that you 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 by primary key (if it exists) or clustered index; if the table has no primary key or clustered index, an error results. 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. Note that 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 prior to obtaining the exclusive lock.

Since both forms of reorganization may modify many pages, the checkpoint log can become large. This can result in a 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.

Side effects

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


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;