Note
This view is available starting with V4.0.0.
Purpose
This view displays the optimizer statistics of indexes of tables owned by the current user.
Applicability
This view is available only in Oracle mode.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| INDEX_NAME | VARCHAR2(128) | NO | The name of the index. |
| TABLE_OWNER | VARCHAR2(128) | NO | The owner of the index. |
| TABLE_NAME | VARCHAR2(128) | NO | The name of the index. |
| PARTITION_NAME | VARCHAR2(128) | NO | The name of the partition. |
| PARTITION_POSITION | NUMBER | NO | The position of the partition in the index. |
| SUBPARTITION_NAME | VARCHAR2(128) | NO | The name of the subpartition. |
| SUBPARTITION_POSITION | NUMBER | NO | The position of the subpartition in the partition. |
| OBJECT_TYPE | VARCHAR2(12) | NO | The type of the object. Valid values: |
| BLEVEL | NUMBER | NO | NULL by default. |
| LEAF_BLOCKS | NUMBER | NO | NULL by default. |
| DISTINCT_KEYS | NUMBER | NO | NULL by default. |
| AVG_LEAF_BLOCKS_PER_KEY | NUMBER | NO | NULL by default. |
| AVG_DATA_BLOCKS_PER_KEY | NUMBER | NO | NULL by default. |
| CLUSTERING_FACTOR | NUMBER | NO | NULL by default. |
| NUM_ROWS | NUMBER | NO | The number of rows in the index. |
| AVG_CACHED_BLOCKS | NUMBER | NO | NULL by default. |
| AVG_CACHE_HIT_RATIO | NUMBER | NO | NULL by default. |
| SAMPLE_SIZE | NUMBER | NO | NULL by default. |
| LAST_ANALYZED | DATE | NO | The date when the index was last analyzed. |
| GLOBAL_STATS | VARCHAR2(3) | NO | Indicates whether statistics are collected or incrementally maintained. Valid values: |
| USER_STATS | VARCHAR2(3) | NO | Indicates whether statistics are directly entered by the user. Valid values: |
| STATTYPE_LOCKED | VARCHAR2(5) | NO | The type of the statistics lock. |
| STALE_STATS | VARCHAR2(3) | NO | Indicates whether the statistics of the object are outdated. |
| SCOPE | VARCHAR2(7) | NO | NULL by default. |
Sample query
Query the optimizer statistics of the TBL1_H_IDX1 index of tables owned by the current user.
obclient [SYS]> SELECT * FROM SYS.USER_IND_STATISTICS WHERE INDEX_NAME='TBL1_H_IDX1'\G
The query result is as follows:
*************************** 1. row ***************************
INDEX_NAME: TBL1_H_IDX1
TABLE_OWNER: SYS
TABLE_NAME: TBL1_H
PARTITION_NAME: NULL
PARTITION_POSITION: NULL
SUBPARTITION_NAME: NULL
SUBPARTITION_POSITION: NULL
OBJECT_TYPE: INDEX
BLEVEL: NULL
LEAF_BLOCKS: NULL
DISTINCT_KEYS: NULL
AVG_LEAF_BLOCKS_PER_KEY: NULL
AVG_DATA_BLOCKS_PER_KEY: NULL
CLUSTERING_FACTOR: NULL
NUM_ROWS: 0
AVG_CACHED_BLOCKS: NULL
AVG_CACHE_HIT_RATIO: NULL
SAMPLE_SIZE: NULL
LAST_ANALYZED: 14-MAR-25
GLOBAL_STATS: NO
USER_STATS: NO
STATTYPE_LOCKED: NULL
STALE_STATS: NO
SCOPE: NULL
*************************** 2. row ***************************
INDEX_NAME: TBL1_H_IDX1
TABLE_OWNER: SYS
TABLE_NAME: TBL1_H
PARTITION_NAME: P0
PARTITION_POSITION: 1
SUBPARTITION_NAME: NULL
SUBPARTITION_POSITION: NULL
OBJECT_TYPE: PARTITION
BLEVEL: NULL
LEAF_BLOCKS: NULL
DISTINCT_KEYS: NULL
AVG_LEAF_BLOCKS_PER_KEY: NULL
AVG_DATA_BLOCKS_PER_KEY: NULL
CLUSTERING_FACTOR: NULL
NUM_ROWS: NULL
AVG_CACHED_BLOCKS: NULL
AVG_CACHE_HIT_RATIO: NULL
SAMPLE_SIZE: NULL
LAST_ANALYZED: NULL
GLOBAL_STATS: NULL
USER_STATS: NULL
STATTYPE_LOCKED: NULL
STALE_STATS: NULL
SCOPE: NULL
*************************** 3. row ***************************
INDEX_NAME: TBL1_H_IDX1
TABLE_OWNER: SYS
TABLE_NAME: TBL1_H
PARTITION_NAME: P1
PARTITION_POSITION: 2
SUBPARTITION_NAME: NULL
SUBPARTITION_POSITION: NULL
OBJECT_TYPE: PARTITION
BLEVEL: NULL
LEAF_BLOCKS: NULL
DISTINCT_KEYS: NULL
AVG_LEAF_BLOCKS_PER_KEY: NULL
AVG_DATA_BLOCKS_PER_KEY: NULL
CLUSTERING_FACTOR: NULL
NUM_ROWS: NULL
AVG_CACHED_BLOCKS: NULL
AVG_CACHE_HIT_RATIO: NULL
SAMPLE_SIZE: NULL
LAST_ANALYZED: NULL
GLOBAL_STATS: NULL
USER_STATS: NULL
STATTYPE_LOCKED: NULL
STALE_STATS: NULL
SCOPE: NULL
*************************** 4. row ***************************
INDEX_NAME: TBL1_H_IDX1
TABLE_OWNER: SYS
TABLE_NAME: TBL1_H
PARTITION_NAME: P2
PARTITION_POSITION: 3
SUBPARTITION_NAME: NULL
SUBPARTITION_POSITION: NULL
OBJECT_TYPE: PARTITION
BLEVEL: NULL
LEAF_BLOCKS: NULL
DISTINCT_KEYS: NULL
AVG_LEAF_BLOCKS_PER_KEY: NULL
AVG_DATA_BLOCKS_PER_KEY: NULL
CLUSTERING_FACTOR: NULL
NUM_ROWS: NULL
AVG_CACHED_BLOCKS: NULL
AVG_CACHE_HIT_RATIO: NULL
SAMPLE_SIZE: NULL
LAST_ANALYZED: NULL
GLOBAL_STATS: NULL
USER_STATS: NULL
STATTYPE_LOCKED: NULL
STALE_STATS: NULL
SCOPE: NULL
4 rows in set
References
To query the statistics of all indexes of tables in the current tenant, see DBA_IND_STATISTICS.
To query the statistics of indexes of tables accessible by the current user, see ALL_IND_STATISTICS.
To query column statistics at the table level, query the following views:
To query column statistics at the global level, query the following views:
To query column statistics at the partition level, query the following views:
To query column statistics at the subpartition level, query the following views:
To query histogram statistics at the table level, query the following views:
To query histogram statistics at the partition level, query the following views:
To query histogram statistics at the subpartition level, query the following views:
For information about how to collect statistics, see the following topics: