Note
This view is available starting with V4.0.0.
Purpose
This view displays the optimizer statistics of indexes on tables accessible to the current user.
Applicability
This view is available only in Oracle mode.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| OWNER | VARCHAR2(128) | NO | The owner of the index. |
| INDEX_NAME | VARCHAR2(128) | NO | The name of the index. |
| TABLE_OWNER | VARCHAR2(128) | NO | The owner of the index object. |
| TABLE_NAME | VARCHAR2(128) | NO | The name of the index object. |
| 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 input by users. 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 on tables accessible to the current user.
obclient [SYS]> SELECT * FROM SYS.ALL_IND_STATISTICS WHERE INDEX_NAME='TBL1_H_IDX1'\G
The query result is as follows:
*************************** 1. row ***************************
OWNER: SYS
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 ***************************
OWNER: SYS
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 ***************************
OWNER: SYS
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 ***************************
OWNER: SYS
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 indexes on tables owned by the current user, see USER_IND_STATISTICS.
To query the statistics of indexes on all tables in the current tenant, see DBA_IND_STATISTICS.
To query the column statistics at the table level, query the following views:
To query the column statistics at the global level, query the following views:
To query the column statistics at the partition level, query the following views:
To query the column statistics at the subpartition level, query the following views:
To query the histogram statistics at the table level, query the following views:
To query the histogram statistics at the partition level, query the following views:
To query the histogram statistics at the subpartition level, query the following views:
For information about how to collect statistics, see the following topics: