【Oracle】インデックス(INDEX)の状態を確認する方法、作成と削除の方法

Oracleでインデックス周りの扱いについてまとめたので、ここにメモしておきます。

インデックスの状態を確認する

インデックスの状態を確認するには「USER_INDEXES」を参照します。SELECT文を発行することで確認することができます。

SELECT * FROM USER_INDEXES;

結果

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
IND_M_USERS_ID NORMAL USER M_USERS TABLE UNIQUE DISABLED   INDEX_TBS 2 255 163840 1048576 1 2147483645           10 YES 2 1344 636120 1 1 42067 VALID 636120 636120 2019/12/20 0:00:00 1 1 NO N Y N DEFAULT DEFAULT DEFAULT NO ​(NULL)​ ​(NULL)​ ​(NULL)​ ​(NULL)​ ​(NULL)​ YES ​(NULL)​ ​(NULL)​ ​(NULL)​ NO NO NO VISIBLE ​(NULL)​ YES
IND_M_USERS_ID NORMAL USER M_USERS TABLE UNIQUE DISABLED   INDEX_TBS 2 255 163840 1048576 1 2147483645           10 YES 2 3003 135256 1 1 18180 VALID 135256 135256 2019/12/20 0:00:00 1 1 NO N Y N DEFAULT DEFAULT DEFAULT NO ​(NULL)​ ​(NULL)​ ​(NULL)​ ​(NULL)​ ​(NULL)​ YES ​(NULL)​ ​(NULL)​ ​(NULL)​ NO NO NO VISIBLE ​(NULL)​ YES
IND_T_SAMPLE01_ID NORMAL USER T_SAMPLE01 TABLE UNIQUE DISABLED   INDEX_TBS 2 255 163840 1048576 1 2147483645           10 YES 1 48 23288 1 1 1049 VALID 23288 23288 2019/12/20 0:00:00 1 1 NO N Y N DEFAULT DEFAULT DEFAULT NO ​(NULL)​ ​(NULL)​ ​(NULL)​ ​(NULL)​ ​(NULL)​ YES ​(NULL)​ ​(NULL)​ ​(NULL)​ NO NO NO VISIBLE ​(NULL)​ YES
IND_T_SAMPLE01_ID NORMAL USER T_SAMPLE01 TABLE UNIQUE DISABLED   INDEX_TBS 2 255 163840 1048576 1 2147483645           10 YES 2 1234 5910 1 1 2075 VALID 5910 5910 2019/12/20 0:00:00 1 1 NO N Y N DEFAULT DEFAULT DEFAULT NO ​(NULL)​ ​(NULL)​ ​(NULL)​ ​(NULL)​ ​(NULL)​ YES ​(NULL)​ ​(NULL)​ ​(NULL)​ NO NO NO VISIBLE ​(NULL)​ YES

 

めちゃくちゃ項目数が多いですね。

最低限の情報が欲しいなら、インデックス名やテーブル名、データサイズのカラムを指定して情報を取得しましょう。

SELECT INDEX_NAME, TABLE_OWNER, TABLE_NAME, STATUS, NUM_ROWS, SAMPLE_SIZE FROM USER_INDEXES;

 

さらにWHERE句にテーブル名を指定することで、テーブル単体のインデックス情報を取得することも可能です。

SELECT INDEX_NAME, TABLE_OWNER, TABLE_NAME, STATUS, NUM_ROWS, SAMPLE_SIZE FROM USER_INDEXES WHERE TABLE_NAME = 'T_SAMPLE01';

 

どのテーブルのどのカラムにインデックスがあるか確認する

どのテーブルのどのカラムにインデックスがあるか確認するには「USER_IND_COLUMNS」を参照します。

SELECT * FROM USER_IND_COLUMNS ORDER BY INDEX_NAME, COLUMN_POSITION;

結果

INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND
IND_M_USERS_ID M_USERS ID 1 6 6 ASC
IND_M_USERS_CODE M_USERS CODE 2 6 6 ASC
IND_T_SAMPLE01_ID T_SAMPLE01 ID 1 6 6 ASC
IND_T_SAMPLE01_CODE T_SAMPLE01 CODE 2 6 6 ASC

 

テーブル名で絞る場合は以下の通り。

SELECT * FROM USER_IND_COLUMNS WHERE TABLE_NAME = 'T_SAMPLE01' ORDER BY INDEX_NAME, COLUMN_POSITION;

 

インデックスの作成・削除

インデックスを実際に作成する場合は、以下のクエリを実行してください。

-- 構文
CREATE INDEX <インデックス名> ON <テーブル名>(列名1 [ASC|DESC], 列名2 [ASC|DESC],・・・) [TABLESPACE 表領域名];
-- 例
CREATE INDEX IND_T_SAMPLE01_NAME ON T_SAMPLE01(T_SAMPLE01_NAME) TABLESPACE index_tbs;

削除のクエリは以下の通りです。

-- 構文
DROP INDEX <インデックス名>;
-- 例
DROP INDEX IND_T_SAMPLE01_NAME;

 



投稿日:2019-12-13    更新日:2019-12-25

[スポンサーリンク]

関連記事
勉強した内容を緩くメモする|JBの技術メモ
勉強した内容を緩くメモする|JBの技術メモ
勉強した内容を緩くメモする|JBの技術メモ
勉強した内容を緩くメモする|JBの技術メモ
勉強した内容を緩くメモする|JBの技術メモ
勉強した内容を緩くメモする|JBの技術メモ
サイト内検索
プロフィール

プロフィール

[Name : じゃぶじゃぶ(@jbjb_2019)]
都内で社内SEをしているおじさん。
仕事で得られる知識だけでは限界を感じ、 WEBの勉強がてらITブログを開始。
サーバからWEBサイトまでフルスクラッチで開発しました。
現在は勉強のモチベーションを保つために活用中。
興味があることを雑記的に書いていきます。

[スポンサーリンク]

[スポンサードリンク]