OracleのInvisibleインデックスについて

2022年11月1日 21:05

この記事を読むとできるようになること

Invisibleインデックスが何かを説明できるようになる
Invisibleインデックスを使ったチューニングができるようになる

はじめに

こんにちは。株式会社SHIFTの小澤です。

インデックスは、基本的にはカーディナリティ度が高い列に張る、ということは分かったとしても、実際に業務で使うような複雑なテーブルに最適なインデックスを張り、かつアプリケーションで最適なSQLを発行するようにベストな開発をする、というのは一発でうまくいくことはあまりないと思います。

まずは、

主キーには自動的にインデックスが作成される
外部キーにもインデックスを作成する必要がある

ここまでは良いとして、後はどの列とどの列にインデックスを張るべきで、それは単一列のインデックスなのか、複合インデックスなのか、不要なインデックスがあれば削除したいもののいったいどれが不要なのかを判断するにはどうすればよいのでしょうか。

アプリケーションが発行しているSQLはすでに大量にあり、業務要件も複雑でER図も多数のテーブルが絡み合っていて机上の検討だけでは検討しきれないことは多いのではないかと思いますが、このような場合はある程度机上で検討したら、実機で検証してみるのが良いと思います。

ただ、実際にインデックスを作成したり削除したりして、その後SQL文を作って実行し、実行時間を取得して実行計画を確認して、などと結構いろいろと作業が必要だし確認することも多くて大変、そもそもテーブルが巨大でインデックス作成も数分かかるのでそんなに気軽にCREATE INDEXもできない、といったような経験はないでしょうか。

このような時にInvisibleインデックスが有効利用できる可能性があります。
DROP INDEXする代わりにインデックスを一時的にオフ(Invisible)にしてインデックスを見えなくする機能です。テーブルへのDML(Insert/Update/Delete)を実行するとインデックスも更新されますが、Invisible状態でも、そのインデックス更新はVisible状態のインデックスの時と同じように行われ、必要に応じてインデックスを素早くオン(Visible)に戻すことができます。

インデックスのオン/オフはOracle側でVisible/Invisibleの状態を切り替えるだけで、物理的にCREATE/DROPを実行するわけではないので、インデックスを数分かけてCREATE/DROPする代わりに、一瞬でインデックスのオン/オフができるということです。

Invisibleインデックスを試してみる

実際に見てみましょう。最初、DEPT_TESTにインデックスを張っていません。 (DEPT_TESTテーブルはDEPTテーブルをコピーして作りました。DEPTテーブルというのはOracle製品で提供されているデモ用のテーブルです。
開発環境ではOracleをインストールする時に一緒にインストールすることが多いと思いますが、詳しくは別の記事で触れようと思います。)

-- DEPT_TESTのINDEXを調べる
SQL> col table_name for a10
SQL> col index_name for a10
SQL> r
  1  select table_name, index_name
  2  from user_indexes
  3* where table_name = 'DEPT_TEST'

no rows selected

SQL> col index_name for a12
SQL> col column_name for a8
SQL> col table_name for a8
SQL> select table_name, index_name, column_name, column_position
  2  from user_ind_columns
  3  where table_name = 'DEPT_TEST'
  4  order by table_name, index_name, column_position;

no rows selected

この状態でDEPT_TESTをSELECTして実行計画を表示し、テーブルフルスキャンを行っていることを確認します。

-- DEPT_TESTをSelectする
SQL> select count(*)
  2  from dept_test
  3  where deptno = 30;

  COUNT(*)
----------
	 1

-- 実行計画を確認する
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID	af1fvrzk0ppnf, child number 0
-------------------------------------
select count(*) from dept_test where deptno = 30

Plan hash value: 2597284190

--------------------------------------------------------------------------------
| Id  | Operation	   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	       |       |       |     3 (100)|	       |
|   1 |  SORT AGGREGATE    |	       |     1 |     3 |	    |	       |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| DEPT_TEST |     1 |     3 |     3	 (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DEPTNO"=30)


19 rows selected.

deptnoにInvisible インデックスを張ります。

-- Invisible INDEXを作成する
SQL> create index dept_test_inv_idx on dept_test(deptno) invisible;

Index created.

ここでSELECTを実行し、実行計画を確認します。
インデックスを張ったが、SELECTからはインデックスが見えていない(Invisible)のでテーブルフルスキャンになっています。

SQL> select count(*)
  2  from dept_test
  3  where deptno = 30;

  COUNT(*)
----------
	 1

SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID	af1fvrzk0ppnf, child number 0
-------------------------------------
select count(*) from dept_test where deptno = 30

Plan hash value: 2597284190

--------------------------------------------------------------------------------
| Id  | Operation	   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	       |       |       |     3 (100)|	       |
|   1 |  SORT AGGREGATE    |	       |     1 |     3 |	    |	       |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| DEPT_TEST |     1 |     3 |     3	 (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DEPTNO"=30)


19 rows selected.

Invisible インデックスであっても、あえてSELECTに見させる方法はあり、それにはヒントを利用します。

SELECT文でUSE_INVISIBLE_INDEXESヒントを使用するか、初期化パラメータOPTIMIZER_USE_INVISIBLE_INDEXESをTRUEに設定します。

-- ヒントを指定してSelect文を実行する
SQL> select /*+ USE_INVISIBLE_INDEXES */ count(*)
  2  from dept_test
  3  where deptno = 30;

  COUNT(*)
----------
	 1

SQL> set lines 200
SQL> set pagesize 200

SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	b0hkrjfdzpgdc, child number 0
-------------------------------------
select /*+ USE_INVISIBLE_INDEXES */ count(*) from dept_test where
deptno = 3

Plan hash value: 3935856867

---------------------------------------------------------------------------------------
| Id  | Operation	  | Name	      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |		      |       |       |     1 (100)|	      |
|   1 |  SORT AGGREGATE   |		      |     1 |     3 | 	   |	      |
|*  2 |   INDEX RANGE SCAN| DEPT_TEST_INV_IDX |     1 |     3 |     1	(0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPTNO"=3)


20 rows selected.

インデックスをVisibleにすると、ヒントを使用する必要はありません。
これは「まあ、それはそうでしょうね」だと思います。

SQL> alter index dept_test_inv_idx visible;

Index altered.

SQL> select count(*)
  2  from dept_test
  3  where deptno = 30;

  COUNT(*)
----------
	 1

SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	af1fvrzk0ppnf, child number 0
-------------------------------------
select count(*) from dept_test where deptno = 30

Plan hash value: 3935856867

---------------------------------------------------------------------------------------
| Id  | Operation	  | Name	      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |		      |       |       |     1 (100)|	      |
|   1 |  SORT AGGREGATE   |		      |     1 |     3 | 	   |	      |
|*  2 |   INDEX RANGE SCAN| DEPT_TEST_INV_IDX |     1 |     3 |     1	(0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPTNO"=30)


19 rows selected.

また、NO_INDEXヒントというものもあり、これを使用するとそのSELECT文でのみインデックスをオフにすることができます。

SQL> select /*+ no_index(dept_test dept_test_inv_idx) */ count(*)
  2  from dept_test
  3  where deptno = 30;

COUNT(*)
----------
	 1

SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	cjc8d18gd1hxf, child number 0
-------------------------------------
select /*+ no_index(dept_test dept_test_inv_idx) */ count(*) from
dept_test where deptno = 30

Plan hash value: 2597284190

--------------------------------------------------------------------------------
| Id  | Operation	   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	       |       |       |     3 (100)|	       |
|   1 |  SORT AGGREGATE    |	       |     1 |     3 |	    |	       |
|*  2 |   TABLE ACCESS FULL| DEPT_TEST |     1 |     3 |     3	 (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DEPTNO"=30)


20 rows selected.

Invisible/Visibleの切り替えはalter indexでいつでもできます。

SQL> alter index dept_test_inv_idx invisible;

Index altered.

インデックスのVislbe/InvisibleはUSER_INDEXESなどで確認できます。

SQL> col index_name for a20
SQL> col visibility for a20
SQL> r
  1  select index_name, visibility
  2  from dba_indexes
  3* where index_name = 'DEPT_TEST_INV_IDX'

INDEX_NAME	     VISIBILITY
-------------------- --------------------
DEPT_TEST_INV_IDX    INVISIBLE

同じ列に複数のインデックスを張ってみる

Invisible インデックスを利用すると、同じ列に複数のインデックスを作成しておいて、日中はインデックス Aを使い、夜間はインデックス Bを使う、というように切り替えて運用することもできます。

ただ、たくさん作ってしまうと、たとえInvisibleであってもインデックスがある分だけDML(INSERT/UPDATE/DELETE)を実行する時にインデックス更新のオーバーヘッドがかかってくるのでやりすぎには注意が必要です。

実際にやってみます。先ほどのDEPT_TESTを使います。

SQL> select * from dept_test;

DEPTNO DNAME	  LOC
---------- -------------- -------------
	10 ACCOUNTING	  NEW YORK
	20 RESEARCH	  DALLAS
	30 SALES	  CHICAGO
	40 OPERATIONS	  BOSTON

SQL> set lines 200
SQL> col table_name for a20
SQL> col index_name for a20
SQL> col index_type for a20
SQL> select table_name, index_name, index_type, uniqueness, status, visibility
  2  from user_indexes
  3  where table_name = 'DEPT_TEST';

TABLE_NAME	     INDEX_NAME 	  INDEX_TYPE	       UNIQUENES STATUS   VISIBILIT
-------------------- -------------------- -------------------- --------- -------- ---------
DEPT_TEST	     DEPT_TEST_INV_IDX	  NORMAL	       NONUNIQUE VALID	  INVISIBLE

SQL> col column_name for a20
SQL> select table_name, index_name, column_position, column_name
  2  from user_ind_columns
  3  where table_name = 'DEPT_TEST'
  4  order by index_name, column_position;

TABLE_NAME	     INDEX_NAME 	  COLUMN_POSITION COLUMN_NAME
-------------------- -------------------- --------------- --------------------
DEPT_TEST	     DEPT_TEST_INV_IDX			1 DEPTNO

一旦インデックスを削除しておきます。

SQL> drop index dept_test_inv_idx online;

Index dropped.

SQL> select table_name, index_name, index_type, uniqueness, status, visibility
  2  from user_indexes
  3  where table_name = 'DEPT_TEST';

no rows selected

ユニークインデックスを作成してみます。
ユニークインデックスを作るとユニーク制約が出来て重複値のInsertができなくなります。

SQL> create unique index dept_test_u1 on dept_test(deptno);

Index created.

SQL> select table_name, index_name, index_type, uniqueness, status, visibility
  2  from user_indexes
  3  where table_name = 'DEPT_TEST';

TABLE_NAME	     INDEX_NAME 	  INDEX_TYPE	       UNIQUENES STATUS   VISIBILIT
-------------------- -------------------- -------------------- --------- -------- ---------
DEPT_TEST	     DEPT_TEST_U1	  NORMAL	       UNIQUE	 VALID	  VISIBLE

deptno(10)がすでにあるところへ重複値(10)をInsertしてみましたが、ユニーク制約違反のためInsertできません。

SQL> insert into dept_test(deptno) values(10);
insert into dept_test(deptno) values(10)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.DEPT_TEST_U1) violated

インデックスがInvisibleであっても重複値のInsertはやはりできません。

SQL> alter index dept_test_u1 invisible;

Index altered.

SQL> select table_name, index_name, index_type, uniqueness, status, visibility
  2  from user_indexes
  3  where table_name = 'DEPT_TEST';

TABLE_NAME	     INDEX_NAME 	  INDEX_TYPE	       UNIQUENES STATUS   VISIBILIT
-------------------- -------------------- -------------------- --------- -------- ---------
DEPT_TEST	     DEPT_TEST_U1	  NORMAL	       UNIQUE	 VALID	  INVISIBLE

SQL> insert into dept_test(deptno) values(10);
insert into dept_test(deptno) values(10)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.DEPT_TEST_U1) violated

インデックスをVisibleに戻しておきます。

SQL> alter index dept_test_u1 visible;

Index altered.

SQL> select table_name, index_name, index_type, uniqueness, status, visibility
  2  from user_indexes
  3  where table_name = 'DEPT_TEST';

TABLE_NAME	     INDEX_NAME 	  INDEX_TYPE	       UNIQUENES STATUS   VISIBILIT
-------------------- -------------------- -------------------- --------- -------- ---------
DEPT_TEST	     DEPT_TEST_U1	  NORMAL	       UNIQUE	 VALID	  VISIBLE

同じ列に(ユニークではない)別のインデックスを作成してみますが、すでにユニークインデックスを張っているのでエラーになります。

SQL> create index dept_test_n1 on dept_test(deptno);
create index dept_test_n1 on dept_test(deptno)
                                       *
ERROR at line 1:
ORA-01408: such column list already indexed

そこで、最初のインデックスをInvisibleにしてみると、その後2番目のインデックスを作成することができるようになります。

SQL> alter index dept_test_u1 invisible;

Index altered.

SQL> create index dept_test_n1 on dept_test(deptno);

Index created.

SQL> select table_name, index_name, index_type, uniqueness, status, visibility
  2  from user_indexes
  3  where table_name = 'DEPT_TEST';

TABLE_NAME	     INDEX_NAME 	  INDEX_TYPE	       UNIQUENES STATUS   VISIBILIT
-------------------- -------------------- -------------------- --------- -------- ---------
DEPT_TEST	     DEPT_TEST_U1	  NORMAL	       UNIQUE	 VALID	  INVISIBLE
DEPT_TEST	     DEPT_TEST_N1	  NORMAL	       NONUNIQUE VALID	  VISIBLE


SQL> select table_name, index_name, column_position, column_name
  2  from user_ind_columns
  3  where table_name = 'DEPT_TEST'
  4  order by index_name, column_position;

TABLE_NAME	     INDEX_NAME 	  COLUMN_POSITION COLUMN_NAME
-------------------- -------------------- --------------- --------------------
DEPT_TEST	     DEPT_TEST_N1			1 DEPTNO
DEPT_TEST	     DEPT_TEST_U1			1 DEPTNO

両方のインデックスをVisibleにすることはできません。
一度にVisibleにできるインデックスは1つのみです。

SQL> alter index dept_test_u1 visible;
alter index dept_test_u1 visible
*
ERROR at line 1:
ORA-14147: There is an existing VISIBLE index defined on the same set of columns.

dept_testをSelectすると、Visibleのインデックスが使われます。

SQL> select deptno
  2  from dept_test
  3  where deptno = 10;

    DEPTNO
----------
	10

SQL> set lines 100
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID	85prxb0x4w39d, child number 0
-------------------------------------
select deptno from dept_test where deptno = 10

Plan hash value: 1203327202

---------------------------------------------------------------------------------
| Id  | Operation	 | Name 	| Rows	| Bytes | Cost (%CPU)| Time	|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |		|	|	|     1 (100)|		|
|*  1 |  INDEX RANGE SCAN| DEPT_TEST_N1 |     1 |     3 |     1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DEPTNO"=10)


18 rows selected.

dept_test_n1をInvisibleにして、Reverse インデックスを追加してみます。

SQL> alter index dept_test_n1 invisible;

Index altered.

SQL> create index dept_test_r1 on dept_test(deptno) reverse;

Index created.

SQL> select table_name, index_name, index_type, uniqueness, status, visibility
  2  from user_indexes
  3  where table_name = 'DEPT_TEST';

TABLE_NAME	     INDEX_NAME 	  INDEX_TYPE	       UNIQUENES STATUS   VISIBILIT
-------------------- -------------------- -------------------- --------- -------- ---------
DEPT_TEST	     DEPT_TEST_U1	  NORMAL	       UNIQUE	 VALID	  INVISIBLE
DEPT_TEST	     DEPT_TEST_N1	  NORMAL	       NONUNIQUE VALID	  INVISIBLE
DEPT_TEST	     DEPT_TEST_R1	  NORMAL/REV	       NONUNIQUE VALID	  VISIBLE

SQL> select table_name, index_name, column_position, column_name
  2  from user_ind_columns
  3  where table_name = 'DEPT_TEST'
  4  order by index_name, column_position;

TABLE_NAME	     INDEX_NAME 	  COLUMN_POSITION COLUMN_NAME
-------------------- -------------------- --------------- --------------------
DEPT_TEST	     DEPT_TEST_N1			1 DEPTNO
DEPT_TEST	     DEPT_TEST_R1			1 DEPTNO
DEPT_TEST	     DEPT_TEST_U1			1 DEPTNO

Function インデックスとBitmap インデックスは同時にVisibleにできます。
dept_test_r1をInvisibleにして、Function INDEXとB-Tree インデックスを追加してみます。

SQL> create bitmap index dept_test_b1 on dept_test(deptno);

Index created.

SQL> create index dept_test_f1 on dept_test(substr(deptno,1,1));

Index created.

SQL> col INDEX_TYPE for a21
SQL> select table_name, index_name, index_type, uniqueness, status, visibility
  2  from user_indexes
  3  where table_name = 'DEPT_TEST';

TABLE_NAME	     INDEX_NAME 	  INDEX_TYPE		UNIQUENES STATUS   VISIBILIT
-------------------- -------------------- --------------------- --------- -------- ---------
DEPT_TEST	     DEPT_TEST_U1	  NORMAL		UNIQUE	  VALID    INVISIBLE
DEPT_TEST	     DEPT_TEST_N1	  NORMAL		NONUNIQUE VALID    INVISIBLE
DEPT_TEST	     DEPT_TEST_R1	  NORMAL/REV		NONUNIQUE VALID    INVISIBLE
DEPT_TEST	     DEPT_TEST_B1	  BITMAP		NONUNIQUE VALID    VISIBLE
DEPT_TEST	     DEPT_TEST_F1	  FUNCTION-BASED NORMAL NONUNIQUE VALID    VISIBLE

SQL> select table_name, index_name, column_position, column_name
  2  from user_ind_columns
  3  where table_name = 'DEPT_TEST'
  4  order by index_name, column_position;

TABLE_NAME	     INDEX_NAME 	  COLUMN_POSITION COLUMN_NAME
-------------------- -------------------- --------------- --------------------
DEPT_TEST	     DEPT_TEST_B1			1 DEPTNO
DEPT_TEST	     DEPT_TEST_F1			1 SYS_NC00004$
DEPT_TEST	     DEPT_TEST_N1			1 DEPTNO
DEPT_TEST	     DEPT_TEST_R1			1 DEPTNO
DEPT_TEST	     DEPT_TEST_U1			1 DEPTNO

【このSHIFT公式ブロガーの最新記事】

＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿

執筆者プロフィール：小澤雅弘
DBMSベンダー、ISPなどでDB関連のプロジェクトを20数年経験。得意領域はDBのパフォーマンスチューニング。

お問合せはお気軽に
https://service.shiftinc.jp/contact/

SHIFTについて（コーポレートサイト）
https://www.shiftinc.jp/

SHIFTのサービスについて（サービスサイト）
https://service.shiftinc.jp/

SHIFTの導入事例
https://service.shiftinc.jp/case/

お役立ち資料はこちら
https://service.shiftinc.jp/resources/

SHIFTの採用情報はこちら
https://recruit.shiftinc.jp/career/

最後まで読んでくださり、ありがとうございます！

御社サービスのご相談はお気軽に。

ソフトウェア品質のプロがお伺いします。