sp_iqindexsize procedure

Function

Gives the size of the specified index.

Syntax

sp_iqindexsize [ [ owner.] table.] index_name 

Permissions

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

Description

Table 7-36: sp_iqindexsize columns

Column name

Description

Username

Index owner.

Indexname

Index for which results are returned, including the table name.

Type

Index type.

Info

Component of the IQ index for which the KBytes, Pages, and Compressed Pages are being reported. The components vary by index type. For example, the default (FP) index includes BARRAY (barray) and Bitmap (bm) components. The Low_Fast (LF) index includes B-tree (bt) and Bitmap (bm) components.

KBytes

Physical object size in KB.

Pages

Number of IQ pages needed to hold the object in memory.

Compressed Pages

Number of IQ pages when the object is compressed (on disk).

Returns the total size of the index in bytes and kilobytes, and an Info column that describes the component of the IQ index for which the KBytes, Pages, and Compressed Pages are reported. The components described vary by index type. For example, the default (FP) index includes BARRAY (barray) and Bitmap (bm) components. The Low_Fast (LF) index includes B-tree (bt) and Bitmap (bm) components.

Also returns the number of pages required to hold the object in memory and the number of IQ pages when the index is compressed (on disk).

You must specify the index_name parameter with this procedure. To restrict results to this index name in a single table, include owner.table. when specifying the index.

Example

sp_iqindexsize ASIQ_IDX_T452_C19_FP

Username

Indexname

Type

Info

KBytes

Pages

Compressed Pages

DBA

Employees.ASIQ_IDX_T452_C19_FP

FP

Total

288

4

2

DBA

Employees.ASIQ_IDX_T452_C19_FP

FP

vdo

0

0

0

DBA

Employees.ASIQ_IDX_T452_C19_FP

FP

bt

0

0

0

DBA

Employees.ASIQ_IDX_T452_C19_FP

FP

garray

0

0

0

DBA

Employees.ASIQ_IDX_T452_C19_FP

FP

bm

136

2

1

DBA

Employees.ASIQ_IDX_T452_C19_FP

FP

barray

152

2

1

DBA

Employees.ASIQ_IDX_T452_C19_FP

FP

dpstore

0

0

0

DBA

Employees.ASIQ_IDX_T452_C19_FP

FP

largelob

0

0

0

See also

Chapter 6, “Using Sybase IQ Indexes” in System Administration Guide: Volume 1.

“FP_LOOKUP_SIZE option” and “MINIMIZE_STORAGE option” in Chapter 2, “Database Options” in Reference: Statements and Options.