Oracle データベース

【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;

結果：

NDEX_NAMETABLE_NAMECOLUMN_NAMECOLUMN_POSITIONCOLUMN_LENGTHCHAR_LENGTHDESCEND
IND_M_USERS_IDM_USERSID166ASC
IND_M_USERS_CODEM_USERSCODE266ASC
IND_T_SAMPLE01_IDT_SAMPLE01ID166ASC
IND_T_SAMPLE01_CODET_SAMPLE01CODE266ASC

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

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;

以上です。

-Oracle, データベース