sp_iqindexsize procedure

Function

Gives the size of the specified index.

Syntax

sp_iqindexsize [ [ owner.] table.] index_name 

Description

Table 10-27: 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 Btree (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 Btree (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

employee.ASIQ_IDX_T452_C19_FP

FP

Total

288

4

2

DBA

employee.ASIQ_IDX_T452_C19_FP

FP

vdo

0

0

0

DBA

employee.ASIQ_IDX_T452_C19_FP

FP

bt

0

0

0

DBA

employee.ASIQ_IDX_T452_C19_FP

FP

garray

0

0

0

DBA

employee.ASIQ_IDX_T452_C19_FP

FP

bm

136

2

1

DBA

employee.ASIQ_IDX_T452_C19_FP

FP

barray

152

2

1

DBA

employee.ASIQ_IDX_T452_C19_FP

FP

dpstore

0

0

0

DBA

employee.ASIQ_IDX_T452_C19_FP

FP

largelob

0

0

0