_IV_TABLE_SPACE System Query

Reply to the _iv_table_space system query contains for each table one row informing about volume of space (disk memory) in the database file (wb8.fil) that is used for table content.

Information provided by this query are also utilized in the Table Statistics window, after pressing the Compute button.

The reply contains following columns:

Column name	Type	Content
`Schema_name`	`CHAR(31)`	Schema name of the (application) where the table belongs. System tables have here NULL value.
`Table_name`	`CHAR(31)`	Table name.
`Main_space`	`BIGINT`	Space size for data of fixed-size columns.
`Valid_quotient`	`REAL`	A part of the `Main_space` space filled with valid records.
`Valid_or_deleted_quotient`	`REAL`	A part of the `Main_space` space filled with valid and deleted records.
`LOB_space`	`BIGINT`	Space size allocated to a variable-size column.
`LOB_valid_quotient`	`REAL`	A part of the `LOB_space` space that belongs to valid records.
`LOB_usage_quotient`	`REAL`	A part of the `LOB_space` utilized for data (the allocated space is rounded up).
`Index_space`	`BIGINT`	Space size allocated for indexes.
`Index_usage_quotient`	`REAL`	A part of the `Index_space` utilized by data.
`Valid_records`	`INT`	Number of valid records.
`Deleted_records )`	`INT`	Number of deleted records; the content can be recovered.
`Free_records`	`INT`	Number of released records; they cannot be recovered any more. New records will be inserted to their place.

The space occupied by the table consists of parts included in Main_space, LOB_space and Index_space. It is listed in bytes. The other data (apart from the number of records) inform of utilization of this space; they are expressed by a real number ranging from 0 to 1.

The space allocated in LOB_space is - because of rounding of disk memory sections - a bit larger than data actually need. The index blocks are also employed only partly, average usage is three quarters (in case of very small tables even less).

Evaluation of this complex query can take a longer time. Therefore it is recommended to constrain it by suitable conditions in the WHERE clause to the schema name and table name.

The database space contains, apart from data in tables, also a free space that is utilized e.g. for temporary tables that are necessary during evaluation of queries. Therefore the sum of table space is less than the database file size.

Individual record states (valid, deleted and released) are described on the Database tables page.

Example:

Find out the space taken by the Tab1 table in the Schema1 application:

SELECT Main_space+LOB_space+Index_space
FROM  _iv_table_space
WHERE schema_name = Schema1  AND table_name = Tab1'

Find out the space taken by all tables from the current application (result in megabytes).

SELECT SUM(Main_space+LOB_space+Index_space)/1024/1024 AS total, SUM(Main_space)/1024/1024 AS fixed_length, 
       SUM(LOB_space)/1024/1024 AS LOBs, SUM(Index_space)/1024/1024 AS indexes
FROM  _iv_table_space
WHERE schema_name = Current_application

Table Statistics Window

The Table Statistics window displays also information about the table that can be obtained even without evaluation of this query. Data like the total number of columns within a table, out of which number of variable-size columns (CLOBs or LOBs), number of indexes, of referential integrity or conditions need not be explained. Two pieces of data can be a bit more complicated:

Allocated space for records: it is an aggregated number of all records within a table (i.e. valid, deleted and released). Individual numbers will display in the bottom list, after pressing Calculate.
The last entry can have two forms: Number of records located in a disc cluster or Number of disc clusters occupied by a record. The first label is displayed if the length of the record fixed part is less than a disk cluster (i.e. 4096 or 8192 bytes), then it indicates, how many times the record could be placed into a cluster. The second case occurs when the record fixed part is greater than disk cluster; then it indicates how many clusters each record takes. It is used for estimation of database size.

_IV_PROFILE System Query

System Queries

_IV_MAIL_PROFS System Query