パーティションとインデックス
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インデックスであっても部分的にインデックスを作成し、必要な場合に限りインデックス・スキャンを行うことができるようになりました。