db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Apache Wiki <wikidi...@apache.org>
Subject [Db-derby Wiki] Update of "OldSpaceTableVti" by StanleyBradbury
Date Mon, 14 May 2012 18:04:14 GMT
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

}}}

Mime
View raw message