Identifying and Reclaiming Unused Space
Page 3 of 4

Identifying Unused Space in a Table or Index

Getting an Estimate of Reclaimable Space

Use the SpaceTable VTI (COM.cloudscape.vti.SpaceTable, aliased as SpaceTable) to get an estimate of the amount of space that can be reclaimed in a table.

The VTI takes a table name as an argument (or the schema name and the table name) and returns a conservative estimate of the space that may be reclaimed by compressing the table and its indexes.

For example, to get space information on a table named FlightAvailability, issue the following query:

SELECT *
FROM NEW SpaceTable('FLIGHTAVAILABILITY') AS EQ

NOTE: The table name must evaluate to a string. For table names created as non-delimited identifiers (non-case-sensitive), specify the table name in uppercase letters.

You may wish to see only the estimate of the space that could be reclaimed by compressing the tables and its indexes. That is the most important information returned by the VTI:

SELECT CONGLOMERATENAME, ESTIMSPACESAVING
FROM NEW SpaceTable('FLIGHTAVAILABILITY') AS EQ

For FlightAvailability, which has two indexes, the output would look something like this if the table has some space that could be reclaimed:

CONGLOMERATENAME |ESTIMSPACESAVING
------------------------------------------------------------
FLIGHTAVAILABILITY |12288
846c00a0-00de-a111-40de-000a0a410d00|8192
ace4c0a3-00de-a111-40de-000a0a410d00|4096

The VTI also has a constructor that takes the schema name followed by the table name. To see unused information about all tables in all schemas, use the following query:

SELECT * FROM SYS.SYSSCHEMAS s, SYS.SYSTABLES t,
   NEW SpaceTable(SCHEMANAME,TABLENAME) AS EQ
WHERE s.SCHEMAID = t.SCHEMAID
AND t.TABLENAME=EQ.CONGLOMERATENAME
AND ISINDEX=false

About the Page Usage Information

The VTI also displays some information about page usage which is less immediately helpful than the estimate of space that could be reclaimed. In looking at the page usage information, for those who want to know, it helps to understand how Cloudscape assigns space to a table or index. When it first creates the conglomerate (table or index), Cloudscape pre-allocates a number of pages for the conglomerate (usually one, but the number is configurable). Cloudscape then allocates pages to the conglomerate, always one page at a time. Allocated pages may or may not contain rows. At some point Cloudscape may flag some of those pages as inefficient users of space. Those pages are called unfilled pages in the VTI. When all the rows are deleted from a page, Cloudscape de-allocates the page and makes it a free page. If the page is needed later, Cloudscape can re-allocate the page. Cloudscape bases its conservative estimate of the amount of space that could be reclaimed on the number of free pages. When you actually compress the table and indexes as described in Reclaiming Unused Space, Cloudscape may also be able to reclaim space from unfilled pages, but this space is not included in the VTI's estimate. Cloudscape is not able to reclaim space from pre-allocated pages that have not yet been allocated.

For example, to see only the page usage information for a table, you could issue the following command:

SELECT NUMALLOCATEDPAGES, NUMFREEPAGES,
    NUMUNFILLEDPAGES, ISINDEX
FROM NEW SpaceTable('FLIGHTAVAILABILITY') AS EQ

The following example output shows that the table has four allocated pages in total (three allocated and one allocated unfilled) and 1 free (de-allocated) pages. Its associated indexes (in the second two rows) also show some free pages.

NUMALLOCATEDPAGES   |NUMFREEPAGES        |NUMUNFILLEDPAGES    |ISINDEX
--------------------------------------------------------------------
1                   |3                   |1                   |false
2                   |2                   |1                   |true
2                   |1                   |1                   |true