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 of OceanBase Database.
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 object type. Valid values:
|
| BLEVEL | NUMBER | NO | Default value: NULL. |
| LEAF_BLOCKS | NUMBER | NO | Default value: NULL. |
| DISTINCT_KEYS | NUMBER | NO | Default value: NULL. |
| AVG_LEAF_BLOCKS_PER_KEY | NUMBER | NO | Default value: NULL. |
| AVG_DATA_BLOCKS_PER_KEY | NUMBER | NO | Default value: NULL. |
| CLUSTERING_FACTOR | NUMBER | NO | Default value: NULL. |
| NUM_ROWS | NUMBER | NO | The number of rows in the index. |
| AVG_CACHED_BLOCKS | NUMBER | NO | Default value: NULL. |
| AVG_CACHE_HIT_RATIO | NUMBER | NO | Default value: NULL. |
| SAMPLE_SIZE | NUMBER | NO | Default value: NULL. |
| LAST_ANALYZED | DATE | NO | The date when the index was last analyzed. |
| GLOBAL_STATS | VARCHAR2(3) | NO | Indicates whether statistics were collected or incrementally maintained. Valid values:
|
| USER_STATS | VARCHAR2(3) | NO | Indicates whether statistics were directly input by the user. Valid values:
|
| STATTYPE_LOCKED | VARCHAR2(5) | NO | The statistics lock type. |
| STALE_STATS | VARCHAR2(3) | NO | Indicates whether the statistics of the object are outdated. |
| SCOPE | VARCHAR2(7) | NO | Default value: NULL. |
Sample query
Query the optimizer statistics of the index TBL1_H_IDX1 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 that you own, see USER_IND_STATISTICS.
To query the statistics of indexes on all tables in the current tenant, see DBA_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: