Optimizes a table and removes any unused space.

Syntax

---

OPTIMIZE TABLE <TableName>
[USING <IndexName>]
[NO BACKUP FILES]

Usage

---

Use this statement to optimize the specified table. Optimization accomplishes three things:

• It removes any unused space from a table and compacts the data.



• It rebuilds all indexes, both system and user-defined, for the table.



• It can optionally physically order the rows in the table according to the most-frequently-used index via the USING clause. This helps ElevateDB optimize the read-ahead of physical rows when executing SQL SELECT queries.

The USING clause is optional. The default behavior is for ElevateDB to physically re-order the rows in the table according to the primary key, or the natural order if there is no primary key defined for the table.

The NO BACKUP FILES clause is optional. Unless this clause is specified, ElevateDB will create backup files (*.old) of the physical table files when optimizing the table.

Examples

---

-- This example optimizes the Customer table
-- and physically orders the rows according to the
-- primary key

OPTIMIZE TABLE Customer

Required Privileges

---

The current user must be granted the MAINTAIN privilege on the current database in order to execute this statement. Please see the User Security topic for more information.

SQL 2003 Standard Deviations

---

This statement deviates from the SQL 2003 standard in the following ways: