Vacuuming an append-optimized table

posted Apr 28, 2017, 3:35 PM by Sachchida Ojha
The VACUUM command collects table-level statistics such as number of rows and pages, so it is also necessary to vacuum append-optimized tables, even when there is no space from updated or deleted rows to reclaim.

Vacuuming an append-optimized table follows a different process than vacuuming heap tables. On each segment, a new segment file is created and visible rows are copied into it from the current segment. When the segment file has been copied, the original is scheduled to be dropped and the new segment file is made available. This requires sufficient available disk space for a copy of the visible rows until the original segment file is dropped.