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] Trivial Update of "OldSpaceTableVti" by OmairAshfaq
Date Mon, 02 Jul 2012 21:54:50 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 OmairAshfaq:
http://wiki.apache.org/db-derby/OldSpaceTableVti?action=diff&rev1=1&rev2=2

Comment:
Added a modified version of the space table query

  ## 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.  
+ 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> 
+ ij>
- SELECT T2.* FROM SYS.SYSTABLES systabs, 
+ SELECT T2.* FROM SYS.SYSTABLES systabs,
- new org.apache.derby.diag.SpaceTable(systabs.tablename) t2 
+ new org.apache.derby.diag.SpaceTable(systabs.tablename) t2
  where t2.isindex = 0;
  
  CONGLOMERATE      |ISIND& |NUMALLOCATED |NUM     |NUMUNFILLED  |PAGESIZE  |ESTIMSPACE
@@ -29, +28 @@

  TESTT              |0     |1            |0        |1             |4096    |0
  TSTTM              |0     |1            |0        |1             |4096    |0
  T_LOB1_LOG         |0     |1            |0        |1             |4096    |0
+ }}}
+ The following query is a slightly modified version of the one above as it calculates the
space used by each conglomerate.
  
+ {{{
+ SELECT v.*, 
+        ((NUMALLOCATEDPAGES + NUMFREEPAGES) * PAGESIZE)  as USEDSPACE 
+ FROM   SYS.SYSSCHEMAS s, SYS.SYSTABLES t,  
+        new org.apache.derby.diag.SpaceTable(SCHEMANAME,TABLENAME) v 
+ WHERE  s.SCHEMAID = t.SCHEMAID;
  }}}
  

Mime
View raw message