作り方
SQL> create unique index <インデックス名> on <テーブル名> (<ユニークにしたいカラム>, <パーティションに使っているカラム>) local;
パーティションに使っているカラムを含めてやるのがポイントみたい。
検証結果
パーティション表を作成する。
SQL> create table t1 (c1 number,c2 number) partition by range(c1) (partition p1 values less than (1000), partition p2 values less than (2000), partition p3 values less than (3000), partition p4 values less than (4000), partition p5 values less than (5000), partition p6 values less than (maxvalue));
ローカルユニークインデックスを作成する。
SQL> create unique index i1 on t1(c2,c1) local;
6000行 insert する。
SQL> begin for i in 1..6000 loop insert into t1 (c1, c2) values(i, 4000-i); end loop; end; / SQL> commit; SQL> select count(1) from t1; COUNT(1) ---------- 6000
ローカル・ユニークインデックスになっているかどうか実行計画を確認する。
SQL> explain plan for select * from t1 where c1 = 1500 and c2 = 2500; SQL> select * from table((dbms_xplan.display(null, null))); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------- Plan hash value: 408458721 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 26 | 1 (0)| 00:00:01 | | | | 1 | PARTITION RANGE SINGLE| | 1 | 26 | 1 (0)| 00:00:01 | 2 | 2 | |* 2 | INDEX UNIQUE SCAN | I1 | 1 | 26 | 1 (0)| 00:00:01 | 2 | 2 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------ 2 - access("C2"=2500 AND "C1"=1500) 14 rows selected.
実行計画を見ると、パーティション・プルーニングが行われて、「INDEX UNIQUE SCAN」になっているのでローカル・ユニークインデックスも使われている。
SQL> explain plan for select * from t1 where c2 = 2500; SQL> select * from table((dbms_xplan.display(null, null))); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------ Plan hash value: 2056491846 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 26 | 7 (0)| 00:00:01 | | | | 1 | PARTITION RANGE ALL| | 1 | 26 | 7 (0)| 00:00:01 | 1 | 6 | |* 2 | INDEX RANGE SCAN | I1 | 1 | 26 | 7 (0)| 00:00:01 | 1 | 6 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------ 2 - access("C2"=2500) Note ----- - dynamic sampling used for this statement 18 rows selected.
グローバル・ユニークインデックスになったりするかもと思って、ユニークキーのみで検索してみたところ、パーティション・プルーニングが行われず、INDEX RANGE SCAN になっているので、無事ローカル・ユニークインデックスになっているぽい。
参考
- データ・ウェアハウスにおけるパーティション化
- パーティション表とパーティション索引
- パーティション・プルーニングの有効性を検証する (1/4)：Oracleパーティショニング実践講座（2） - ＠IT
- 索引およびクラスタの使用方法
追記:
SQL> alter table t1 add constraint uk1 unique (c1,c2) using index i1;
開発チームの方々がこんな感じで、ローカル・ユニークインデックスを利用してパーティション表全体に UNIQUE 制約をかけられていた。頭いい！メモメモ。
一意でない索引による一意性の規定
UNIQUE制約、またはPRIMARY KEY制約の一意性の側面に関して、一意性を規定するために、表の既存の一意でない索引を使用できます。このアプローチの利点は、制約が使用禁止にされているときでも索引が使用可能であり、妥当であるということです。したがって、使用禁止にされているUNIQUE制約またはPRIMARY KEY制約を使用可能にするために、その制約に対応付けられているUNIQUE索引を再作成する必要はありません。これにより、大規模表で操作を使用可能にする際に時間を大幅に節約できます。
さらに、一意でない索引を使用して一意性を規定すると、索引の重複を排除できます。主キー列がすでにコンポジット索引の同一キーとして組み込まれている場合、その列に対する一意索引は不要です。Oracleでは、制約を使用可能または規定するときに、既存の索引を使用できます。索引を複製しないので、領域を大幅に節約できます。ただし、既存の索引がパーティション化されている場合は、索引のパーティション・キーもUNIQUEキーのサブセットである必要があります。サブセットでなければ、Oracleはさらに一意索引を追加作成し、制約を規定します。
索引およびクラスタの使用方法
