Package org.apache.derby.diag

Class SpaceTable

  • All Implemented Interfaces:
    java.lang.AutoCloseable, java.sql.ResultSet, java.sql.Wrapper, AwareVTI, VTICosting
    public class SpaceTable
extends VTITemplate
implements VTICosting
    SpaceTable is a virtual table that shows the space usage of a particular table and its indexes. This virtual table can be invoked by calling it directly, and supplying the schema name and table name as arguments. 
     SELECT * FROM TABLE(SYSCS_DIAG.SPACE_TABLE('MYSCHEMA', 'MYTABLE')) T;
    If the schema name is not supplied, the default schema is used. 
     SELECT * FROM TABLE(SYSCS_DIAG.SPACE_TABLE('MYTABLE')) T;

    NOTE: Both the schema name and the table name must be any expression that evaluates to a string data type. If you created a schema or table name as a non-delimited identifier, you must present their names in all upper case.

    The SpaceTable virtual table can be used to estimate whether space might be saved by compressing a table and its indexes.

    The SpaceTable virtual table has the following columns:

    • CONGLOMERATENAME varchar(128) - nullable. The name of the conglomerate, which is either the table name or the index name. (Unlike the SYSCONGLOMERATES column of the same name, table ID's do not appear here).
    • ISINDEX SMALLINT - not nullable. Is not zero if the conglomerate is an index, 0 otherwise.
    • NUMALLOCATEDPAGES bigint - not nullable. The number of pages actively linked into the table. The total number of pages in the file is the sum of NUMALLOCATEDPAGES + NUMFREEPAGES.
    • NUMFREEPAGES bigint - not nullable. The number of free pages that belong to the table. When a new page is to be linked into the table the system will move a page from the NUMFREEPAGES list to the NUMALLOCATEDPAGES list. The total number of pages in the file is the sum of NUMALLOCATEDPAGES + NUMFREEPAGES.
    • NUMUNFILLEDPAGES bigint - not nullable. The number of unfilled pages that belong to the table. Unfilled pages are allocated pages that are not completely full. Note that the number of unfilled pages is an estimate and is not exact. Running the same query twice can give different results on this column.
    • PAGESIZE integer - not nullable. The size of the page in bytes for that conglomerate.
    • ESTIMSPACESAVING bigint - not nullable. The estimated space which could possibly be saved by compressing the conglomerate, in bytes.
    • TABLEID char(36) - not nullable. The UUID of the table.

    To get space information on all schemas and tables, use a query such as 

        select v.*
    from SYS.SYSSCHEMAS s,
         SYS.SYSTABLES t,
         TABLE(SYSCS_DIAG.SPACE_TABLE(SCHEMANAME, TABLENAME)) v
    where s.SCHEMAID = t.SCHEMAID;

    • Field Detail

      • initialized

        boolean initialized

      • currentRow

        int currentRow

      • wasNull

        private boolean wasNull

      • schemaName

        private java.lang.String schemaName

      • tableName

        private java.lang.String tableName

      • metadata

        private static final java.sql.ResultSetMetaData metadata

    • Constructor Detail

      • SpaceTable

        public SpaceTable()

      • SpaceTable

        public SpaceTable​(java.lang.String schemaName,
                  java.lang.String tableName)

      • SpaceTable

        public SpaceTable​(java.lang.String tableName)

    • Method Detail

      • getMetaData

        public java.sql.ResultSetMetaData getMetaData()
        Specified by:
        getMetaData in interface java.sql.ResultSet
        Overrides:
        getMetaData in class VTITemplate
        See Also:
        ResultSet.getMetaData()

      • next

        public boolean next()
             throws java.sql.SQLException
        Specified by:
        next in interface java.sql.ResultSet
        Throws:
        java.sql.SQLException - if no transaction context can be found
        See Also:
        ResultSet.next()

      • close

        public void close()
        Specified by:
        close in interface java.lang.AutoCloseable
        Specified by:
        close in interface java.sql.ResultSet
        See Also:
        ResultSet.close()

      • getString

        public java.lang.String getString​(int columnNumber)
        Specified by:
        getString in interface java.sql.ResultSet
        Overrides:
        getString in class VTITemplate
        See Also:
        ResultSet.getString(int)

      • getLong

        public long getLong​(int columnNumber)
        Specified by:
        getLong in interface java.sql.ResultSet
        Overrides:
        getLong in class VTITemplate
        See Also:
        ResultSet.getLong(int)

      • getShort

        public short getShort​(int columnNumber)
        Specified by:
        getShort in interface java.sql.ResultSet
        Overrides:
        getShort in class VTITemplate
        See Also:
        ResultSet.getShort(int)

      • getInt

        public int getInt​(int columnNumber)
        Specified by:
        getInt in interface java.sql.ResultSet
        Overrides:
        getInt in class VTITemplate
        See Also:
        ResultSet.getInt(int)

      • wasNull

        public boolean wasNull()
        Specified by:
        wasNull in interface java.sql.ResultSet
        Overrides:
        wasNull in class VTITemplate
        See Also:
        ResultSet.wasNull()