ASA SQL Reference
SQL Statements
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
[ { PRIMARY KEY
| FOREIGN KEY foreign_key_name
| INDEX index_name }
| ORDER {ON | OFF}
]
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 the group is being processed; once the group is finished, a checkpoint may occur. The rows are processed in order by primary key; if the table has no primary key, 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. This form of the statement can only be used with databases created with Adaptive Server Anywhere version 7.0 or above. 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. For version 7.0 or earlier databases, this may result in growth of the database file. For version 8.0 or later databases, this will result in only a temporary increase in the database file size, since the checkpoint log is deleted at shutdown and the file is truncated at that point. Also, more contiguous allocation of table pages may result for version 8.0 or later databases.
Neither form of the statement is logged to the transaction log.
Must be either the owner of the table, or a user with DBA authority.
Prior to starting the reorganization, a checkpoint is done to try to maximize the number of free pages.
The following example reorganizes the employee table according to the primary key.
REORGANIZE TABLE employee PRIMARY KEY