Oracleの「パーティション索引」を図解する

はじめに

Oracle Databaseには「索引のパーティション化」という機能があります。
索引パーティションには、下記3タイプがあります。

• ローカル同一キー索引
• ローカル非同一キー索引
• グローバル同一キー索引

しかし、それぞれの索引がどのような意味を持つのかが分かりにくかったので、図で表してみました。

環境

Oracle Database Enterprise Edition 19c を使用しました。

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

また、3つのパーティション（P_JAN, P_FEB, P_MAR）に分けられた表を作成し、それぞれのパーティションに20レコードを挿入しました。

--パーティション表を作成
SQL> create table scott.table1 (
  2    id    number ,  
  3    day date)
  4    PARTITION BY RANGE (day) 
  5    INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
  6      ( PARTITION P_JAN VALUES LESS THAN (TO_DATE('2019-02-01', 'YYYY-MM-DD')),
  7        PARTITION P_FEB VALUES LESS THAN (TO_DATE('2019-03-01', 'YYYY-MM-DD')),
  8        PARTITION P_MAR VALUES LESS THAN (TO_DATE('2019-04-01', 'YYYY-MM-DD'))
  9      );

表が作成されました。

SQL> begin
  2      for i in 1..20 loop
  3          insert into scott.table1 values(scott.seq1.nextval, to_date ('2019-01-' || i , 'YYYY-MM-DD'));
  4          insert into scott.table1 values(scott.seq1.nextval, to_date ('2019-02-' || i,  'YYYY-MM-DD'));
  5          insert into scott.table1 values(scott.seq1.nextval, to_date ('2019-03-' || i,  'YYYY-MM-DD'));
  6      end loop ;
  7  end;
  8  /

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

SQL> commit;

コミットが完了しました。

SQL> select * from scott.table1 order by id;

    ID DAY
---------- ----------
     1 2019-01-01
     2 2019-02-01
     3 2019-03-01
     4 2019-01-02
     5 2019-02-02
     6 2019-03-02
     ：
    58 2019-01-20
    59 2019-02-20
    60 2019-03-20

60行が選択されました。

--NUM_ROWS確認のため統計情報を収集
SQL> exec dbms_stats.gather_table_stats ('SCOTT', 'TABLE1') ;

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

--それぞれのパーティションに20レコードが格納されていることを確認
SQL> select table_name , partition_name, num_rows from  user_tab_partitions ;

TABLE_NAME PARTITION_   NUM_ROWS
---------- ---------- ----------
TABLE1     P_FEB          20
TABLE1     P_JAN          20
TABLE1     P_MAR          20

作成したパーティション・テーブルを図にすると下記のような状態です。

ローカル同一キー索引

表のパーティション化キー、索引のパーティション化キー、索引を作成する列が全て同じ場合にはローカル同一キー索引を使用します。

作成方法

--ローカル同一キー索引作成
SQL> create index local_part_idx ON table1 (day) local ;

索引が作成されました。

図にするとこう↓

メリット

パーティション単位で索引を再構築（リビルト）できるため、メンテナンスが容易になります。

--パーティションP_JAN の索引を再構築
SQL> alter index scott.local_part_idx rebuild partition P_JAN ;

索引が変更されました。

ローカル非同一キー索引

索引を作成する列と表のパーティション化キーが異なる場合には、ローカル非同一キー索引を使用します。

作成方法

--ローカル非同一キー索引作成
SQL> create index local_part_idx2 ON table1 (id) local ;

索引が作成されました。

図にするとこう↓

メリット

ローカル同一キー索引と同様、パーティション単位で索引を再構築できるためメンテナンスが容易になります。
（他にもあると思われる）

グローバル同一キー索引

表のパーティション化キーと索引のパーティション化キーが異なる場合には、グローバル同一キー索引を使用します。
尚、表のパーティション数と索引のパーティション数を一致させる必要はありません。

作成方法

--グローバル同一キー索引作成
SQL> CREATE INDEX global_idx ON table1 (id)
  2     GLOBAL PARTITION BY RANGE (id)
  3        (PARTITION p1_15     VALUES LESS THAN (16),
  4         PARTITION p16_30   VALUES LESS THAN (31),
  5         PARTITION p31_45   VALUES LESS THAN (46),
  6         PARTITION p46_60   VALUES LESS THAN (MAXVALUE));

索引が作成されました。

図にするとこう↓

メリット

パーティション化キー（今回はDAY列）ではない列（今回はID列）をwhere句に指定した場合でもパーティション・プルーニングが行われます。

--グローバル同一キー索引がない場合
SQL> select * from scott.table1 where id = 1 ;

    ID DAY
---------- ----------
     1 2019-01-01


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

----------------------------------------------------------------------------------------------
| Id  | Operation       | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |     1 |    11 |   820   (1)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL|        |     1 |    11 |   820   (1)| 00:00:01 |     1 |1048575|
|*  2 |   TABLE ACCESS FULL | TABLE1 |     1 |    11 |   820   (1)| 00:00:01 |     1 |1048575|
----------------------------------------------------------------------------------------------


--グローバル同一キー索引がある場合
SQL> select * from scott.table1 where id = 1 ;

    ID DAY
---------- ----------
     1 2019-01-01


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

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |        |     1 |    11 |     2   (0)| 00:00:01 |   |   |
|   1 |  PARTITION RANGE SINGLE             |        |     1 |    11 |     2   (0)| 00:00:01 |     1 |     1 |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| TABLE1     |     1 |    11 |     2   (0)| 00:00:01 | ROWID | ROWID |
|*  3 |    INDEX RANGE SCAN             | GLOBAL_IDX |     1 |   |     1   (0)| 00:00:01 |     1 |     1 |
--------------------------------------------------------------------------------------------------------------------------

※Pstart=Pstop=1 であることから、1つのパーティションにのみアクセスしたことが分かります。

まとめ

最後に、それぞれの索引がどのような場合に使用されるかのまとめを記載します。