Query indexes

You can query indexes by using system views, including USER_INDEXES, ALL_INDEXES, DBA_INDEXES, ALL_IND_COLUMNS, DBA_IND_COLUMNS, and USER_IND_COLUMNS.

• Query the indexes on all tables of a user

  obclient> SELECT * FROM USER_INDEXES WHERE table_name='TEST';
  +-------------+------------+-------------+------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+
  | 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 |
  +-------------+------------+-------------+------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+
  | T5_NAME_IND | NORMAL     | SYS         | TEST       | 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            |
  +-------------+------------+-------------+------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+
  1 row in set (0.00 sec)
  

  TABLE_NAME: the name of the table that is queried, which is TEST in this example.

  INDEX_TYPE: the index type. The value NORMAL indicates a normal index.

  UNIQUENESS: indicates whether the index is a unique index. The value NONUNIQUE indicates that this index is not a unique index.

• Query the indexes on a table

  obclient> SELECT * FROM ALL_INDEXES WHERE table_name='TEST';
  +-------+-------------+------------+-------------+------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+
  | 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 |
  +-------+-------------+------------+-------------+------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+
  | SYS   | T5_NAME_IND | NORMAL     | SYS         | TEST       | 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            |
  +-------+-------------+------------+-------------+------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+
  1 row in set (0.00 sec)
  

  COMPRESSION: indicates whether index compression is enabled. The default value is ENABLED.

  PREFIX_LENGTH: the number of fields compressed during index compression.

  INCLUDE_COLUMN: the column ID of the last column in the primary key of the index-organized table.

  DISTINCT_KEYS: the number of unique index values.

  DROPPED: indicates whether the index is dropped and moved to the recycle bin.

• Query the indexes on all tables in the current database

  obclient> SELECT * FROM DBA_INDEXES;
  +-------+---------------------------------------+------------+-------------+-------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+
  | 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 |
  +-------+---------------------------------------+------------+-------------+-------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+
  | SYS   | TABLE_NAME1_OBPK_1639997002986248     | NORMAL     | SYS         | TABLE_NAME1 | TABLE      | UNIQUE     | 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            |
  +-------+---------------------------------------+------------+-------------+-------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+
  5 rows in set (0.01 sec)
  

  OWNER: the current database user, which is SYS in this example.

  INDEX_TYPE: the index type. The value NORMAL indicates a normal index.

  UNIQUENESS: indicates whether the index is a unique index.

• Query the columns of indexes on all tables accessible to the current user

  bclient> SELECT * FROM ALL_IND_COLUMNS;
  +-------------+---------------------------------------+-------------+-------------+-------------+-----------------+---------------+-------------+---------+
  | INDEX_OWNER | INDEX_NAME                            | TABLE_OWNER | TABLE_NAME  | COLUMN_NAME | COLUMN_POSITION | COLUMN_LENGTH | CHAR_LENGTH | DESCEND |
  +-------------+---------------------------------------+-------------+-------------+-------------+-----------------+---------------+-------------+---------+
  | SYS         | TABLE_NAME1_OBPK_1639997002986248     | SYS         | TABLE_NAME1 | W_ID        |               1 |            22 |           0 | ASC     |
  +-------------+---------------------------------------+-------------+-------------+-------------+-----------------+---------------+-------------+---------+
  12 rows in set (0.02 sec)
  

  • DESCEND: The value ASC indicates that the indexed columns are sorted in ascending order.

  • COLUMN_NAME: the column name.

• Query the columns of indexes on all tables in the current database

  obclient> SELECT * FROM DBA_IND_COLUMNS;
  +-------------+---------------------------------------+-------------+-------------+-------------+-----------------+---------------+-------------+---------+
  | INDEX_OWNER | INDEX_NAME                            | TABLE_OWNER | TABLE_NAME  | COLUMN_NAME | COLUMN_POSITION | COLUMN_LENGTH | CHAR_LENGTH | DESCEND |
  +-------------+---------------------------------------+-------------+-------------+-------------+-----------------+---------------+-------------+---------+
  | SYS         | TABLE_NAME1_OBPK_1639997002986248     | SYS         | TABLE_NAME1 | W_ID        |               1 |            22 |           0 | ASC     |
  +-------------+---------------------------------------+-------------+-------------+-------------+-----------------+---------------+-------------+---------+
  12 rows in set (0.02 sec)
  

  • INDEX_OWNER: the index owner.

  • INDEX_NAME: the index name.

• Query the details of indexes on a table

  obclient> SELECT * FROM USER_IND_COLUMNS WHERE table_name='TEST';
  +-------------+------------+-------------+-----------------+---------------+-------------+---------+
  | INDEX_NAME  | TABLE_NAME | COLUMN_NAME | COLUMN_POSITION | COLUMN_LENGTH | CHAR_LENGTH | DESCEND |
  +-------------+------------+-------------+-----------------+---------------+-------------+---------+
  | T5_NAME_IND | TEST       | NAME        |               1 |             1 |           1 | ASC     |
  +-------------+------------+-------------+-----------------+---------------+-------------+---------+
  1 row in set (0.02 sec)
  

  • COLUMN_POSITION: the column position.

  • COLUMN_LENGTH: the column length.

  • CHAR_LENGTH: the character length.