インデックスの確認

システムビューを使用してインデックスを確認できます。インデックスを確認するためのビューには、USER_INDEXES、ALL_INDEXES、DBA_INDEXES、ALL_IND_COLUMNS、DBA_IND_COLUMNS、USER_IND_COLUMNSがあります。

• ユーザーが所有するすべてのテーブルのインデックス情報を確認するために使用されます。

  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 は、クエリ対象のテーブル名として TEST を指定します。

  INDEX_TYPE はインデックスタイプを示し、NORMAL は通常のインデックスを表します。

  UNIQUENESS はインデックスが一意であるかどうかを示します。このフィールドに NONUNIQUE が表示されている場合、一意ではありません。

• テーブルのすべてのインデックスを確認するために使用されます。

  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 フィールド情報は、インデックス圧縮機能が有効になっているかどうかを示します。デフォルトは ENABLED 状態です。

  PREFIX_LENGTH は、インデックス圧縮で圧縮されるフィールド数を示します。

  INCLUDE_COLUMN フィールドには、インデックス組織化テーブルの主キー（オーバーフローなし）インデックスの最後の列の列IDが含まれます。

  DISTINCT_KEYS は異なるインデックス値の数を表します。

  DROPPED は、インデックスが削除され、ゴミ箱にあるかどうかを示します。

• データベース内のすべてのテーブルのインデックス情報を確認するために使用されます。

  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 このフィールドは、現在のデータベースのユーザーが SYS であることを示します。

  INDEX_TYPE はインデックスタイプを示し、NORMAL は通常のインデックスを表します。

  UNIQUENESS は、重複を許さない一意のインデックスを示します。

• ユーザーがアクセス可能なすべてのテーブルのインデックス列情報を確認するために使用されます。

  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 このフィールドは、ASC が昇順インデックスを示します。

  • COLUMN_NAME は列名を表します。

• データベース内のすべてのテーブルのインデックス列情報を確認するために使用されます。

  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 はインデックスの所有者を示します。

  • INDEX_NAME はインデックス名です。

• テーブルのインデックスの詳細情報を確認するために使用されます。

  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 は列の位置情報を表します。

  • COLUMN_LENGTH は列長を示します。

  • CHAR_LENGTH は文字長を示します。