部分インデックスを試す (Oracle Database 12c)

# パーティションとインデックス
 PartitioningオプションはOracle Database Enterprise Editionの有償オプションです。大規模なテーブルをパーティションに分割することでI/Oを削減し、パフォーマンスを向上できる可能性があります。
一方で、検索性能を向上させる機能としてインデックスがあります。しかし、大規模なテーブルに対するGLOBALインデックスの作成は作成時間が長くかかり、インデックスの保存領域も大きくなります。このためOracle Database 12cでは一部のパーティションのみインデックスを作成しない「部分インデックス」の機能を利用することができます。

# 部分インデックスを使う準備
 部分インデックスを使うためには、まずインデックスを作成しないパーティションに対してINDEXING OFF句を指定します。この指定を省略した場合はINDEXING ONがデフォルトです。テーブルに対してデフォルト値を指定することもできます。

下記の例ではパーティションp2に対してINDEXING OFFを指定しています。確認は{USER|ALL|DBA}_TAB_PARTITIONSビューのINDEXING列を参照します。

```
SQL> CREATE TABLE part1 (loc CHAR(2), id NUMBER, val VARCHAR2(10))
PARTITION BY LIST (loc)
(
  PARTITION p1 VALUES ('CA'),
  PARTITION p2 VALUES ('AZ') INDEXING OFF,
  PARTITION p3 VALUES ('IA')
);

表が作成されました。

SQL> SELECT PARTITION_NAME, INDEXING FROM USER_TAB_PARTITIONS ;

PARTITION_NAME                 INDE
------------------------------ ----
P1                             ON
P2                             OFF
P3                             ON

SQL>


```

# 部分インデックスを使わない（デフォルト）
インデックスを作成する場合、INDEXING FULLを指定するか省略するとパーティションのINDEXING句とは無関係にインデックスが作成されます。

下記の例ではINDEXING FULLを指定しており、インデックスのサイズは4,608ブロックです。インデックスのINDEXINGの指定は{ALL|USER|DBA}_INDEXESビューのINDEXING列で確認できます。

```
SQL> CREATE INDEX idx1_part1 ON part1 (id) GLOBAL INDEXING FULL;

索引が作成されました。

SQL> SELECT INDEX_NAME, INDEXING FROM USER_INDEXES WHERE INDEX_NAME='IDX1_PART1';

INDEX_NAME                     INDEXIN
------------------------------ -------
IDX1_PART1                     FULL

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'PART1');

PL/SQLプロシージャが正常に完了しました。

SQL>  SELECT SEGMENT_NAME, BLOCKS FROM USER_SEGMENTS WHERE SEGMENT_NAME='IDX1_PART1';

SEGMENT_NAME                       BLOCKS
------------------------------ ----------
IDX1_PART1                           4608

SQL>
```

# 部分インデックスを作る
部分インデックスを作成する場合は、CREATE INDEX文にINDEXING PARTIALを指定します。インデックスのサイズは2,816ブロックに削減されていることがわかります。

```

SQL> CREATE INDEX idx1_part1 ON part1 (id) GLOBAL INDEXING PARTIAL;

索引が作成されました。

SQL> SELECT INDEX_NAME, INDEXING FROM USER_INDEXES WHERE INDEX_NAME='IDX1_PART1';

INDEX_NAME                     INDEXIN
------------------------------ -------
IDX1_PART1                     PARTIAL

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'PART1');

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT SEGMENT_NAME, BLOCKS FROM USER_SEGMENTS WHERE SEGMENT_NAME='IDX1_PART1';

SEGMENT_NAME                       BLOCKS
------------------------------ ----------
IDX1_PART1                           2816

SQL>

```

# 実行計画の確認
パーティション・プルーニングができる場合の実行計画を確認します。インデックスが作成されているパーティション（p1）を検索する場合にはINDEX RANGE SCANになっています。

```
SQL> SELECT * FROM part1 WHERE loc='CA' AND id=1000;

LO         ID VAL
-- ---------- ----------
CA       1000 1000


実行計画
----------------------------------------------------------
Plan hash value: 1732713264

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |            |     1 |    15 |     3   (0)| 00:00:01 |       |       |
|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| PART1      |     1 |    15 |     3   (0)| 00:00:01 |     1 |     1 |
|*  2 |   INDEX RANGE SCAN                 | IDX1_PART1 |     2 |       |     2   (0)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------

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

   1 - filter("LOC"='CA')
   2 - access("ID"=1000)


統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        697  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
```

一方でインデックスが作成されていないパーティション（p2）を検索する場合にはTABLE ACCESS FULLになっています。

```
SQL> SELECT * FROM PART1 WHERE loc='AZ' AND id=1000;

LO         ID VAL
-- ---------- ----------
AZ       1000 1000


実行計画
----------------------------------------------------------
Plan hash value: 1427294587

-----------------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |    15 |   574   (2)| 00:00:09 |       |       |
|   1 |  PARTITION LIST SINGLE|       |     1 |    15 |   574   (2)| 00:00:09 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL   | PART1 |     1 |    15 |   574   (2)| 00:00:09 |     2 |     2 |
-----------------------------------------------------------------------------------------------

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

   2 - filter("ID"=1000)


統計
----------------------------------------------------------
          5  recursive calls
          0  db block gets
       1416  consistent gets
          0  physical reads
          0  redo size
        697  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
```
上記のようにGLOBALインデックスであっても部分的にインデックスを作成し、必要な場合に限りインデックス・スキャンを行うことができるようになりました。