Displays index metadata for a given index.
You can optionally restrict the output to only those indexes on a specified table, and to only those indexes belonging to a specified owner.
User must be a table owner, have REFERENCE permissions on the table, or have either the ALTER ANY INDEX or ALTER ANY OBJECT system privilege.
Specifying a table name limits output to those indexes belonging to that table. Specifying an owner name limits output to indexes owned by that owner. Omitted parameters default to NULL. You can specify only one index per procedure.
User supplier IQ UNIQUE value for the column is available through sp_iqindexmetadata. It reports exact cardinality if Unique HG or LF are present. It reports 0 as cardinality if (only) non-unique HG is present.
The first row of output is the owner name, table name, and index name for the index.
Subsequent rows of output are specific to the type of index specified.
Index type |
Metadata returned |
|---|---|
CMP, DATE, DTTM, TIME |
Type, Version |
FP |
Type, Style, Version, DBType, Maximum Width, EstUnique, TokenCount, NBit, CountSize, DictSize, CountLen, MaxKeyToken, MinKey Token, MinCount, MaxCount, DistinctKey, IQ Unique |
HG |
Type, Version, Maintains Exact Distinct, Level 0 Threshold, Force Physical Delete, Maximum Level Count, Tier ratio, Auto sizing, Average Load Size (records), Active Subindex count, Cardinality Range Min - Max, Estimated Cardinality, Accuracy of Cardinality |
HNG |
Type, Version, BitsPerBlockmap, NumberOfBits |
LF |
Type, Version, IndexStatus, NumberOfBlockmaps, BitsPerBlockmap, Distinct Keys |
WD |
Type, Version, KeySize, Delimiters, DelimiterCount, MaxKeyWordLength, PermitEmptyWord |
| Value1 | Value2 | Value3 |
|---|---|---|
| DBA | test2 | test2_c1_hg |
| Type | HG | |
| Version | 3 | |
| Maintains Exact Distinct | No | |
| Level 0 Threshold | 3000000 | |
| Force Physical Delete | Yes | |
| Maximum Level Count | 10 | |
| Tier ratio | 30 | |
| Auto sizing | On | |
| Avarage Load Size (records) | 58622 | |
| Active Subindex count | 3 | |
| Cardinality Range Min - Max | 5-5 | |
| Estimated Cardinality | 5 | |
| Accuracy of Cardinality | 100 | |
| Level: 0 Main Index Total Row Count | 1 | |
| Level: 0 Main Index Deleted Row Count | 0 | |
| Level: 0 Main Index # of Btree Pages in Main btree | 1 | |
| Level: 0 Main Index # of Garray Pages | 1 | |
| Level: 0 Main Index # of Keys in Main Btree | 1 | |
| Level: 0 Main Index # of Keys Probed in Btree | 0 | |
| Level: 0 Main Index # of Keys Found Duplicate in Btree | 0 | |
| Level: 0 Main Index # of Keys Possible Distinct in Btree | 0 | |
| Level: 1 Main Index Total Row Count | 3145747 | |
| Level: 1 Main Index Deleted Row Count | 0 | |
| Level: 1 Main Index # of Btree Pages in Main btree | 1 | |
| Level: 1 Main Index # of Btree Pages in Conjugate btree | 1 | |
| Level: 1 Main Index # of Garray Pages | 2 | |
| Level: 1 Main Index # of Keys in Main Btree | 8 | |
| Level: 1 Main Index # of Keys in Conjugate Btree | 3 | |
| Level: 1 Main Index # rows in Conjugate Btree | 2949127 | |
| Level: 1 Main Index # of Keys Probed in Btree | 0 | |
| Level: 1 Main Index # of Keys Found Duplicate in Btree | 0 | |
| Level: 1 Main Index # of Keys Possible Distinct in Btree | 0 | |
| Level: 1 Incremental Index Total Row Count | 1 | |
| Level: 1 Incremental Index Deleted Row Count | 0 | |
| Level: 1 Incremental Index # of Btree Pages | 1 | |
| Level: 1 Incremental Index # of Garray Pages | 1 | |
| Level: 1 Incremental Index #of Keys in Btree | 1 | |
| Level: 1 Incremental Index # of Keys Probed in Btree | 0 | |
| Level: 1 Incremental Index # of Keys Found Duplicate in Btree | 0 | |
| Level: 1 Incremental Index # of Keys Possible Distinct in Btree | 0 |