Groom tables

As part of your routine database maintenance activities, plan to recover disk space that is occupied by outdated or deleted rows. In normal operation, an update or delete of a table row does not remove the old tuple (version of the row). This approach benefits multiversion concurrency control by retaining tuples that can potentially be visible to other transactions. Over time however, the outdated or deleted tuples are of no interest to any transaction. After you capture them in a backup, you can reclaim the space that they occupy by using the SQL GROOM TABLE command.

Note: Use the GROOM TABLE command to maintain the user tables by reclaiming disk space for deleted or outdated rows, and to reorganize the tables by their organizing keys. The GROOM TABLE command processes and reorganizes the table records in each data slice in a series of steps. Users can do tasks such as SELECT, UPDATE, DELETE, and INSERT operations while the data grooming is taking place. The SELECT and INSERT operations run in parallel with the groom steps; any UPDATE and DELETE operations run serially between the groom steps. For details about the GROOM TABLE command, see the GROOM TABLE command.
Keep in mind the following when you groom tables to reclaim disk space:
  • Groom tables that receive frequent updates or deletes more often than tables that are seldom updated.
  • If you have a mixture of large tables, some of which are heavily updated and others that are seldom updated, you might want to set up periodic tasks that routinely groom the frequently updated tables.
  • Grooming deleted records has no effect on your database statistics because the process physically removes records that were already “logically” deleted. When you groom a table, the system leaves the min/max, null, and estimated dispersion values unchanged.
  • Reclaiming records does affect where the remaining records in the table are located. The system updates the zone map accordingly.
  • If you truncated a table and there are in-flight transactions that started before the TRUNCATE query, note that the groom process does not reclaim the truncated rows until after the last in-flight transaction has committed or aborted.
Tip: When you delete all the contents of a table, consider using the TRUNCATE command rather than the DELETE command, which eliminates the need to run the GROOM TABLE command.