You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by Apache Wiki <wi...@apache.org> on 2012/05/14 20:04:14 UTC
[Db-derby Wiki] Update of "OldSpaceTableVti" by StanleyBradbury
Dear Wiki user,
You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification.
The "OldSpaceTableVti" page has been changed by StanleyBradbury:
http://wiki.apache.org/db-derby/OldSpaceTableVti
New page:
## Submitted May 14, 2012
In version 10.3 the SYSCS_DIAG diagnostic tables and functions were introduced. For versions 10.3 and above use SYSCS_DIAG.SPACE_TABLE.
But for people concerned with the earlier versions of Derby the SpaceTable VTI can used to examine the space usage of a particular table and its indexes. This VTI is not documented because it did not meet the standards based requirement to be part of Derby. Regardless it is very useful though it is unsupported and all that that implies.
The example query below shows how to list the space table information for the tables in a database. To include the information on indexes remove the where-clause.
{{{
ij>
SELECT T2.* FROM SYS.SYSTABLES systabs,
new org.apache.derby.diag.SpaceTable(systabs.tablename) t2
where t2.isindex = 0;
CONGLOMERATE |ISIND& |NUMALLOCATED |NUM |NUMUNFILLED |PAGESIZE |ESTIMSPACE
NAME PAGES FREEPAGES PAGES SAVING
---------------------------------------------------- -----------------------------------
AIRLINES |0 |1 |0 |1 |4096 |0
CITIES |0 |2 |0 |0 |4096 |0
COUNTRIES |0 |3 |0 |1 |4096 |0
FLIGHTAVAILABILITY |0 |8 |0 |0 |4096 |0
FLIGHTS |0 |15 |0 |0 |4096 |0
FLIGHTS_HISTORY |0 |1 |0 |1 |4096 |0
LOB1 |0 |672 |0 |1 |32768 |0
MAPS |0 |10 |0 |1 |32768 |0
TEST |0 |1 |0 |1 |4096 |0
TESTT |0 |1 |0 |1 |4096 |0
TSTTM |0 |1 |0 |1 |4096 |0
T_LOB1_LOG |0 |1 |0 |1 |4096 |0
}}}