org.apache.derby.diag
Class SpaceTable

java.lang.Object
  org.apache.derby.vti.VTITemplate
      org.apache.derby.diag.SpaceTable

All Implemented Interfaces:

java.sql.ResultSet, 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.

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 Summary

private static ResultColumnDescriptor[]	columnInfo
private ConglomInfo[]	conglomTable
(package private) int	currentRow
(package private) boolean	initialized
private static java.sql.ResultSetMetaData	metadata
private java.lang.String	schemaName
private SpaceInfo	spaceInfo
private java.lang.String	tableName
private TransactionController	tc
private boolean	wasNull

Fields inherited from interface org.apache.derby.vti.VTICosting

defaultEstimatedCost, defaultEstimatedRowCount

Fields inherited from interface java.sql.ResultSet

CLOSE_CURSORS_AT_COMMIT, CONCUR_READ_ONLY, CONCUR_UPDATABLE, FETCH_FORWARD, FETCH_REVERSE, FETCH_UNKNOWN, HOLD_CURSORS_OVER_COMMIT, TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, TYPE_SCROLL_SENSITIVE

Constructor Summary

SpaceTable(java.lang.String tableName)

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

Method Summary

void	close()
private void	getConglomInfo(LanguageConnectionContext lcc)
double	getEstimatedCostPerInstantiation(VTIEnvironment vtiEnvironment) Get the estimated cost for a single instantiation of a Table Function.
double	getEstimatedRowCount(VTIEnvironment vtiEnvironment) Get the estimated row count for a single scan of a Table Function.
int	getInt(int columnNumber)
long	getLong(int columnNumber)
java.sql.ResultSetMetaData	getMetaData()
short	getShort(int columnNumber)
private void	getSpaceInfo(int index)
java.lang.String	getString(int columnNumber)
boolean	next()
boolean	supportsMultipleInstantiations(VTIEnvironment vtiEnvironment) Find out if the ResultSet of the Table Function can be instantiated multiple times.
boolean	wasNull()

Methods inherited from class org.apache.derby.vti.VTITemplate

absolute, afterLast, beforeFirst, cancelRowUpdates, clearWarnings, deleteRow, findColumn, first, getArray, getArray, getAsciiStream, getAsciiStream, getBigDecimal, getBigDecimal, getBigDecimal, getBigDecimal, getBinaryStream, getBinaryStream, getBlob, getBlob, getBoolean, getBoolean, getByte, getByte, getBytes, getBytes, getCharacterStream, getCharacterStream, getClob, getClob, getConcurrency, getCursorName, getDate, getDate, getDate, getDate, getDouble, getDouble, getFetchDirection, getFetchSize, getFloat, getFloat, getInt, getLong, getObject, getObject, getObject, getObject, getRef, getRef, getRow, getShort, getStatement, getString, getTime, getTime, getTime, getTime, getTimestamp, getTimestamp, getTimestamp, getTimestamp, getType, getUnicodeStream, getUnicodeStream, getURL, getURL, getWarnings, insertRow, isAfterLast, isBeforeFirst, isFirst, isLast, last, moveToCurrentRow, moveToInsertRow, previous, refreshRow, relative, rowDeleted, rowInserted, rowUpdated, setFetchDirection, setFetchSize, updateArray, updateArray, updateAsciiStream, updateAsciiStream, updateBigDecimal, updateBigDecimal, updateBinaryStream, updateBinaryStream, updateBlob, updateBlob, updateBoolean, updateBoolean, updateByte, updateByte, updateBytes, updateBytes, updateCharacterStream, updateCharacterStream, updateClob, updateClob, updateDate, updateDate, updateDouble, updateDouble, updateFloat, updateFloat, updateInt, updateInt, updateLong, updateLong, updateNull, updateNull, updateObject, updateObject, updateObject, updateObject, updateRef, updateRef, updateRow, updateShort, updateShort, updateString, updateString, updateTime, updateTime, updateTimestamp, updateTimestamp

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

conglomTable

private ConglomInfo[] conglomTable

initialized

boolean initialized

currentRow

int currentRow

wasNull

private boolean wasNull

schemaName

private java.lang.String schemaName

tableName

private java.lang.String tableName

spaceInfo

private SpaceInfo spaceInfo

tc

private TransactionController tc

columnInfo

private static final ResultColumnDescriptor[] columnInfo

metadata

private static final java.sql.ResultSetMetaData metadata

Constructor Detail

SpaceTable

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

SpaceTable

public SpaceTable(java.lang.String tableName)

Method Detail

getConglomInfo

private void getConglomInfo(LanguageConnectionContext lcc)
                     throws StandardException

Throws:

StandardException

getSpaceInfo

private void getSpaceInfo(int index)
                   throws StandardException

Throws:

StandardException

getMetaData

public java.sql.ResultSetMetaData getMetaData()

Specified by:

getMetaData in interface java.sql.ResultSet

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.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()

getEstimatedRowCount

public double getEstimatedRowCount(VTIEnvironment vtiEnvironment)

Description copied from interface: VTICosting

Get the estimated row count for a single scan of a Table Function.

Specified by:

getEstimatedRowCount in interface VTICosting

Parameters:

vtiEnvironment - The state variable for optimizing the Table Function.

Returns:

The estimated row count for a single scan of the Table Function.

See Also:

VTICosting.getEstimatedRowCount(org.apache.derby.vti.VTIEnvironment)

getEstimatedCostPerInstantiation

public double getEstimatedCostPerInstantiation(VTIEnvironment vtiEnvironment)

Description copied from interface: VTICosting

Get the estimated cost for a single instantiation of a Table Function.

Specified by:

getEstimatedCostPerInstantiation in interface VTICosting

Parameters:

vtiEnvironment - The state variable for optimizing the Table Function.

Returns:

The estimated cost for a single instantiation of the Table Function.

See Also:

VTICosting.getEstimatedCostPerInstantiation(org.apache.derby.vti.VTIEnvironment)

supportsMultipleInstantiations

public boolean supportsMultipleInstantiations(VTIEnvironment vtiEnvironment)

Description copied from interface: VTICosting

Find out if the ResultSet of the Table Function can be instantiated multiple times.

Specified by:

supportsMultipleInstantiations in interface VTICosting

Parameters:

vtiEnvironment - The state variable for optimizing the Table Function.

Returns:

true

See Also:

VTICosting.supportsMultipleInstantiations(org.apache.derby.vti.VTIEnvironment)