Methods to reduce table fragmentation

The following methods help control table fragmentation:

  • Use PCTFREE   SQL Anywhere reserves extra room on each page to allow rows to grow slightly. When an update to a row causes it to grow beyond the original space allocated for it, the row is split and the initial row location contains a pointer to another page where the entire row is stored. For example, filling empty rows with UPDATE statements or inserting new columns into a table can lead to severe row splitting. As more rows are stored on separate pages, more time is required to access the additional pages.

    You can reduce the amount of fragmentation in your tables by specifying the percentage of space in a table page that should be reserved for future updates. This PCTFREE specification can be set with CREATE TABLE, ALTER TABLE, DECLARE LOCAL TEMPORARY TABLE, or LOAD TABLE. See PCTFREE clause.

  • Reorganize tables   You can defragment specific tables using the REORGANIZE TABLE statement or clicking Reorganize on the Fragmentation tab in Sybase Central. See REORGANIZE TABLE statement or Using the Fragmentation tab (SQL Anywhere plug-in).

  • Rebuild the database   Rebuilding the database defragments all tables, including system tables, provided the rebuild is performed as a two-step process, that is, data is unloaded and stored to disk, and then reloaded. Rebuilding in this manner also has the benefit of rearranging the table rows so they appear in the order specified by the clustered index and primary keys. One-step rebuilds (for example, using the -ar, -an, or -ac options), do not reduce table fragmentation. See Rebuilding databases and Unload utility (dbunload).


Using the Fragmentation tab (SQL Anywhere plug-in)