About Defragmentation

TimesTen stores table rows in pages and assigns new pages for the existing tables. Over time, inserting, updating, and deleting rows can fragment these pages, causing the database to allocate more space than required.

This can result in an inability to allocate memory for other uses (such as new pages for other tables) due to a lack of free memory. In such cases, the database is defragmented to free up memory for other uses.

The two methods to defragment a TimesTen database are:

• Using ttMigrate utility - This method involves taking the database offline to perform the defragmentation. The ttMigrate utility migrates data from the current database to a new database, reorganizing it and eliminating fragmentation during the transfer. The ttMigrate utility defragments the entire database including the out-of-line data. The traditional method uses the ttMigrate utility, which can be risky especially for an active standby pair since the active database must operate without a replica while the standby database undergoes migration.

  ttMigrate does consolidate table partitions created by ALTER TABLE ADD. A secondary table partition is created after a table has been altered with the ALTER TABLE ADD SQL statement. Defragmentation enables you to remove the secondary table partitions and create a single table partition that contains all of the table columns. When secondary table partitions have been created, it is recommended to periodically defragment the database in order to improve space utilization and performance. See Defragmentation with the ttMigrate Utility.

• Online defragmentation - This method allows the database to remain operational while defragmentation occurs in the background. Online defragmentation defragments the inline data of user tables but does not defragment the out-of-line data. Online defragmentation does not require an active standby replication configuration or database duplication. It is performed in-place, eliminating the need for database downtime. The key advantage of online defragmentation is that it allows the operation to occur without interrupting system availability, making it the preferred approach over ttMigrate, which relies on an active standby pair to minimize downtime.

  Online defragmentation does not consolidate table partitions created by ALTER TABLE ADD. See Online Defragmentation of TimesTen Databases.