sp_iqtablesize procedure

Returns the size of the specified table.

sp_iqtablesize ( table_owner.table_name )

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.

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. Note that 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 IQ does not write empty pages. IQ pages compress well, regardless of the fullness of the page.

call sp_iqtablesize ('dba.tab1')