![]() |
Identifying and Reclaiming Unused Space
|
|
Identifying Unused Space in a Table or IndexGetting an Estimate of Reclaimable SpaceUse 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 * 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 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
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, About the Page Usage InformationThe 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, 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 |
|
![]() Cloudscape Version 3.6 For information about Cloudscape technical support, go to: www.cloudscape.com/support/.Copyright © 1998, 1999, 2000 Informix Software, Inc. All rights reserved. |