602SQL Documentation Index  

_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.


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: