株式会社コーソル

コーソルDatabaseエンジニアのブログ

技術ブログ

12c新機能 同一列に対する複数索引

Oracle Database 12cより 同じ列に対して複数の索引を作成できるようになりました。 例えば、同じ列に対してBツリー索引とビットマップ索引を作成できます。従来は 同じ列に対してBツリー索引とビットマップ索引を作成しようとすると、 ORA-01408: such column list already indexed というエラーが発生していました。

ただし、この機能は、Oracle Database 11g R1 で導入された不可視索引と連携して 使用することを想定した機能です。私は、この機能を、不可視索引がOracle Database 11g において不十分であった点を補う位置づけであるととらえています。

さっそく動作を確認してみましょう。まず、テーブルとデータを準備します。 

SQL> create table tab2 (n number, c varchar(80));
Table created.

SQL> insert into tab2 select 1, 'aaa' from dual
  2    connect by level <= 999;

999 rows created.

SQL> insert into tab2 values(999, 'aaa');

1 row created.

SQL> commit;

Commit complete.

テーブルtab2のn列に対して、Bツリー索引idx2tとビットマップ索引idx2bを作成しようとしますが・・・ 

SQL> create index idx2t on tab2(n);

Index created.

SQL> create bitmap index idx2b on tab2(n);
create bitmap index idx2b on tab2(n)
                                  *
ERROR at line 1:
ORA-01408: such column list already indexed

過去バージョンと同様にORA-01408が発生してビットマップ索引を作成できません。同じ列に複数の索引を作成するためには、索引の作成前に既存の索引をINVISIBLEにする必要があるのです。

さて、一応この状態で、索引が使用されることを確認しておきます。 

 
SQL> exec dbms_stats.gather_table_stats(null, 'TAB2');

PL/SQL procedure successfully completed.

SQL> explain plan for SELECT count(*) FROM tab2 WHERE n = 1;

Explained.

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1847674165

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |     4 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |       |     1 |     4 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX2T |   500 |  2000 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - access("N"=1)

14 rows selected.

Bツリー索引が使われていることがわかります。

このBツリー索引をINVISIBLEにして、同じ列にビットマップ索引を作成します。 

SQL> alter index idx2t invisible;

Index altered.

SQL> create bitmap index idx2b on tab2(n);

Index created.

今度はビットマップ索引を正常に作成できます。

不可視索引にアクセスするかどうかを制御するoptimizer_use_invisible_indexesはデフォルトでFALSEです。この状態では、不可視(INVISIBLE)状態の索引は使われません。 

SQL> show parameter optimizer_use_invisible_indexes

NAME                                 TYPE
------------------------------------ -----------
VALUE
----------------------------------------------------------------------------------------------------
optimizer_use_invisible_indexes      boolean
FALSE
SQL> explain plan for SELECT count(*) FROM tab2 WHERE n = 1;

Explained.

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 703723870

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     1 |     4 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |       |     1 |     4 |            |          |
|   2 |   BITMAP CONVERSION COUNT     |       |   500 |  2000 |     1   (0)| 00:00:01 |
|*  3 |    BITMAP INDEX FAST FULL SCAN| IDX2B |       |       |            |          |
---------------------------------------------------------------------------------------

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

   3 - filter("N"=1)

15 rows selected.

不可視索引を使用しようと、明示的にヒントでその索引を指定しても、使用されません。 

SQL> explain plan for SELECT /*+ INDEX(tab2 idx2t) */ count(*) FROM tab2 WHERE n = 999;

Explained.

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1400846200

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     4 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE             |       |     1 |     4 |            |          |
|   2 |   BITMAP CONVERSION COUNT   |       |   500 |  2000 |     1   (0)| 00:00:01 |
|*  3 |    BITMAP INDEX SINGLE VALUE| IDX2B |       |       |            |          |
-------------------------------------------------------------------------------------

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

   3 - access("N"=999)

15 rows selected.

不可視索引を使用するには、optimizer_use_invisible_indexes を TRUEに設定する必要があります。 

SQL> ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;

Session altered.

SQL> explain plan for SELECT /*+ INDEX(tab2 idx2t) */ count(*) FROM tab2 WHERE n = 999;

Explained.

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1847674165

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |     4 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |       |     1 |     4 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX2T |   500 |  2000 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - access("N"=999)

14 rows selected.

SQL> explain plan for SELECT count(*) FROM tab2 WHERE n = 1;

Explained.

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 703723870

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     1 |     4 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |       |     1 |     4 |            |          |
|   2 |   BITMAP CONVERSION COUNT     |       |   500 |  2000 |     1   (0)| 00:00:01 |
|*  3 |    BITMAP INDEX FAST FULL SCAN| IDX2B |       |       |            |          |
---------------------------------------------------------------------------------------

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

   3 - filter("N"=1)

15 rows selected.

関連記事はありません

プロフィール

On7tWW6m1Ul4

渡部 亮太

・Oracle ACE
・AWS Certified Solutions Architect - Associate
・ORACLE MASTER Platinum Oracle Database 11g, 12c 他多数

カテゴリー

アーカイブ

一覧へ戻る