ASA SQL User's Guide
  Monitoring and Improving Performance
    Fragmentation

Table fragmentation

When rows are not stored contiguously, or if rows are split onto more than one page, performance decreases because these rows require additional page accesses. Table fragmentation is distinct from file fragmentation.

Adaptive Server 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.

For more information, see CREATE TABLE statement, ALTER TABLE statement, DECLARE LOCAL TEMPORARY TABLE statement, and LOAD TABLE statement.

You can use the sa_table_fragmentation stored procedure to obtain information about the degree of fragmentation of your database tables. You must have DBA authority to run this procedure. The following statement calls the sa_table_fragmentation stored procedure:

CALL sa_table_fragmentation (['table_name' [,'owner_name']])

For more information, see sa_table_fragmentation system procedure.

For more information about unloading a database, see Unloading a database using the dbunload command-line utility.

For more information about rebuilding a database, see The Rebuild utility.

To defragment individual tables

• Execute a REORGANIZE TABLE statement.

For more information, see REORGANIZE TABLE statement.