Note
This view is available starting with V2.2.30.
Purpose
The ALL_INDEXES view displays the index column information of all tables accessible to the current user.
Applicability
This view is available only in Oracle-compatible 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. |
| INDEX_TYPE | VARCHAR2(27) | NO | The type of the index. |
| TABLE_OWNER | VARCHAR2(128) | NO | The owner of the object being indexed. |
| TABLE_NAME | VARCHAR2(128) | NO | The name of the object being indexed. |
| TABLE_TYPE | CHAR(5) | NO | The type of the object being indexed. |
| UNIQUENESS | VARCHAR2(9) | NO | Indicates whether the index is unique (UNIQUE) or nonunique. |
| COMPRESSION | VARCHAR2(13) | NO | Indicates whether the index is compressed. |
| PREFIX_LENGTH | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| TABLESPACE_NAME | VARCHAR2(30) | NO | The name of the tablespace where the index is stored. |
| INI_TRANS | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| MAX_TRANS | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| INITIAL_EXTENT | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| NEXT_EXTENT | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| MIN_EXTENTS | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| MAX_EXTENTS | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| PCT_INCREASE | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| PCT_THRESHOLD | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| INCLUDE_COLUMN | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| FREELISTS | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| FREELIST_GROUPS | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| PCT_FREE | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| LOGGING | VARCHAR2(3) | NO | This column is not supported. It is set to NULL by default. |
| BLEVEL | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| LEAF_BLOCKS | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| DISTINCT_KEYS | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| AVG_LEAF_BLOCKS_PER_KEY | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| AVG_DATA_BLOCKS_PER_KEY | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| CLUSTERING_FACTOR | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| STATUS | VARCHAR2(8) | NO | Indicates whether the index is valid or unusable. |
| NUM_ROWS | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| SAMPLE_SIZE | NUMBER | NO | This column is not supported. It is set to NULL by default. |
| LAST_ANALYZED | DATE | NO | This column is not supported. It is set to NULL by default. |
| DEGREE | VARCHAR2(40) | NO | This column is not supported and is NULL by default. |
| INSTANCES | VARCHAR2(40) | NO | This column is not supported and is NULL by default. |
| PARTITIONED | VARCHAR2(3) | NO | Indicates whether the index is a partitioned index. |
| TEMPORARY | VARCHAR2(1) | NO | This column is not supported and is NULL by default. |
| GENERATED | VARCHAR2(1) | NO | This column is not supported and is NULL by default. |
| SECONDARY | VARCHAR2(1) | NO | This column is not supported and is NULL by default. |
| BUFFER_POOL | VARCHAR2(7) | NO | This column is not supported and is NULL by default. |
| FLASH_CACHE | VARCHAR2(7) | NO | This column is not supported and is NULL by default. |
| CELL_FLASH_CACHE | VARCHAR2(7) | NO | This column is not supported and is NULL by default. |
| USER_STATS | VARCHAR2(3) | NO | This column is not supported and is NULL by default. |
| DURATION | VARCHAR2(15) | NO | This column is not supported and is NULL by default. |
| PCT_DIRECT_ACCESS | NUMBER | NO | This column is not supported and is NULL by default. |
| ITYP_OWNER | VARCHAR2(128) | NO | This column is not supported and is NULL by default. |
| ITYP_NAME | VARCHAR2(128) | NO | This column is not supported and is NULL by default. |
| PARAMETERS | VARCHAR2(1000) | NO | This column is not supported and is NULL by default. |
| GLOBAL_STATS | VARCHAR2(3) | NO | This column is not supported and is NULL by default. |
| DOMIDX_STATUS | VARCHAR2(12) | NO | This column is not supported and is NULL by default. |
| DOMIDX_OPSTATUS | VARCHAR2(6) | NO | This column is not supported and is NULL by default. |
| FUNCIDX_STATUS | VARCHAR2(8) | NO | The status of the function-based index. |
| JOIN_INDEX | VARCHAR2(3) | NO | Indicates whether the index is a join index. |
| IOT_REDUNDANT_PKEY_ELIM | VARCHAR2(3) | NO | This column is not supported and is NULL by default. |
| DROPPED | VARCHAR2(3) | NO | Indicates whether the index has been dropped and is in the recycle bin. |
| VISIBILITY | VARCHAR2(9) | NO | Indicates whether the index is visible to the optimizer. |
| DOMIDX_MANAGEMENT | VARCHAR2(14) | NO | This column is not supported and is NULL by default. |
| SEGMENT_CREATED | VARCHAR2(3) | NO | This column is not supported and is NULL by default. |
| ORPHANED_ENTRIES | VARCHAR2(3) | NO | This column is not supported and is NULL by default. |
| INDEXING | VARCHAR2(7) | NO | This column is not supported and is NULL by default. |
| AUTO | VARCHAR2(3) | NO | This column is not supported and is NULL by default. |
Sample query
Query the index information of the TBL2 table in the tables accessible to the current user.
obclient [SYS]> SELECT * FROM SYS.ALL_INDEXES WHERE table_name='TBL2';
The query result is as follows:
+-------+------------+------------+-------------+------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+------------------+----------+------+
| OWNER | INDEX_NAME | INDEX_TYPE | TABLE_OWNER | TABLE_NAME | TABLE_TYPE | UNIQUENESS | COMPRESSION | PREFIX_LENGTH | TABLESPACE_NAME | INI_TRANS | MAX_TRANS | INITIAL_EXTENT | NEXT_EXTENT | MIN_EXTENTS | MAX_EXTENTS | PCT_INCREASE | PCT_THRESHOLD | INCLUDE_COLUMN | FREELISTS | FREELIST_GROUPS | PCT_FREE | LOGGING | BLEVEL | LEAF_BLOCKS | DISTINCT_KEYS | AVG_LEAF_BLOCKS_PER_KEY | AVG_DATA_BLOCKS_PER_KEY | CLUSTERING_FACTOR | STATUS | NUM_ROWS | SAMPLE_SIZE | LAST_ANALYZED | DEGREE | INSTANCES | PARTITIONED | TEMPORARY | GENERATED | SECONDARY | BUFFER_POOL | FLASH_CACHE | CELL_FLASH_CACHE | USER_STATS | DURATION | PCT_DIRECT_ACCESS | ITYP_OWNER | ITYP_NAME | PARAMETERS | GLOBAL_STATS | DOMIDX_STATUS | DOMIDX_OPSTATUS | FUNCIDX_STATUS | JOIN_INDEX | IOT_REDUNDANT_PKEY_ELIM | DROPPED | VISIBILITY | DOMIDX_MANAGEMENT | SEGMENT_CREATED | ORPHANED_ENTRIES | INDEXING | AUTO |
+-------+------------+------------+-------------+------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+------------------+----------+------+
| SYS | TBL2_IDX1 | NORMAL | SYS | TBL2 | TABLE | NONUNIQUE | ENABLED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | VALID | NULL | NULL | NULL | 1 | NULL | NO | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NO | NULL | NO | VISIBLE | NULL | NULL | NULL | NULL | NULL |
+-------+------------+------------+-------------+------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+------------------+----------+------+
1 row in set
References
Query the index information of all tables owned by the current user: USER_INDEXES
Query the index information of all tables in the current tenant: DBA_INDEXES
For more information about the operations of an index, see View an index.