sp_iqtablesize procedure

Function

Returns the size of the specified table.

Syntax

sp_iqtablesize ( table_owner.table_name )

Permissions

DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.

Description

Returns the total size of the table in KBytes and NBlocks (IQ blocks). Also returns the number of pages required to hold the table in memory, and the number of IQ pages that are compressed when the table is compressed (on disk). You must specify the table_name parameter with this procedure. If you are the owner of table_name, then you do not have to specify the table_owner parameter.

Table 7-60: sp_iqtablesize columns

Column name

Description

Ownername

Name of owner

Tablename

Name of table

Columns

Number of columns in the table

KBytes

Physical table size in KB

Pages

Number of IQ pages needed to hold the table in memory

CompressedPages

Number of IQ pages that are compressed, when the table is compressed (on disk)

NBlocks

Number of IQ blocks

Pages is the total number of IQ pages for the table. The unit of measurement for pages is IQ page size. All in-memory buffers (buffers in the IQ buffer cache) are the same size.

IQ pages on disk are compressed. Each IQ page on disk uses 1 to 16 blocks. If the IQ page size is 128KB, then the IQ block size is 8KB. In this case, an individual on-disk page could be 8, 16, 24, 32, 40, 48, 56, 64, 72, 80, 88, 96, 104, 112, 120, or 128 KB.

If you divide the KBytes value by page size, you see the average on-disk page size.

NoteSybase IQ always reads and writes an entire page, not blocks. For example, if an individual page compresses to 88K, then IQ reads and writes the 88K in one I/O. The average page is compressed by a factor of 2 to 3.

NBlocks is Kbytes divided by IQ block size.

CompressedPages is the number of pages that are compressed. For example, if Pages is 1000 and CompressedPages is 992, this means that 992 of the 1000 pages are compressed. CompressedPages divided by Pages is usually near 100%, because most pages compress. An empty page is not compressed, since Sybase IQ does not write empty pages. IQ pages compress well, regardless of the fullness of the page.

Example

call sp_iqtablesize ('dba.emp1')

Ownername

Tablename

Columns

KBytes

Pages

CompressedPages

NBlocks

DBA

emp1

4

1504

24

14

188