Using Nonunique Indexes to Enforce Uniqueness Tweet

久々のブログ更新ですが、今回は意外と知らない方が多いOracleの機能について。 :)

Oracleが既存の非ユニーク索引を利用してユニーク制約や主キー制約を実施するという機能を持っているっていうことを知らない方って意外と多いんですよね。Oracle® Database Performance Tuning Guide 11g Release 2 (11.2) - 14.1.9 Using Nonunique Indexes to Enforce Uniqueness

この機能を有効に活用している方も多いですが、この機能を知らないで、ユニーク制約作成したのにuser_indexesを見たらユニーク索引が作成されていないって慌てる方もたまにいるので、そろそろ書いておこうかな。と。　制約が既存の非ユニーク索引を利用して一意制約を実施するということにどのようなメリットがあるのか？ってことはマニュアルに書いてるから読んでね。（記載されているマニュアルがDatabase Performance Tuning Guideなので、感のいい方なら、どのような事が書かれているか想像はつくと思いますが・・　:)

では、早速！

・まず、テスト用の表を作って。

SCOTT>
 SCOTT> create table hoge (
  2  a number not null
  3  ,b number not null
  4  ,c number not null
  5  );

表が作成されました。

・当然ですけど、この時点では索引もユニーク制約や主キー制約も存在しません。

SCOTT> select index_name,uniqueness,table_name,status from user_indexes where table_name='HOGE';

レコードが選択されませんでした。

SCOTT> select constraint_name,constraint_type,index_name,table_name from user_constraints where table_name='HOGE' and constraint_type in ('P','U');

レコードが選択されませんでした。

・主キー制約とユニーク制約を追加してみます。あまり良い例ではないけど、まあ、そのあたりは気にしないでください。 :)

SCOTT> alter table hoge add constraint pk_hoge primary key (a);

表が変更されました。

SCOTT> alter table hoge add constraint uk_hoge unique (b);   

表が変更されました。

・主キー制約又は、ユニーク制約を実施可能な既存索引が存在しないので追加した主キー制約及び、ユニーク制約を実施するため、ユニーク索引が各制約毎に作成されたことが確認できる。

SCOTT> select constraint_name,constraint_type,index_name,table_name from user_constraints where table_name='HOGE' and constraint_type in ('P','U');

CONSTRAINT_NAME 	       C INDEX_NAME			TABLE_NAME
------------------------------ - ------------------------------ ----------------------------------
PK_HOGE 		       P PK_HOGE			HOGE
UK_HOGE 		       U UK_HOGE			HOGE

SCOTT> select index_name,uniqueness,table_name from user_indexes where table_name='HOGE';

INDEX_NAME		       UNIQUENES TABLE_NAME
------------------------------ --------- ------------------------------
PK_HOGE		               UNIQUE    HOGE
UK_HOGE 		       UNIQUE	 HOGE

・主キー制約とユニーク制約を削除すると、制約作成に伴い作成された一意索引も同時に削除される。

SCOTT> alter table hoge drop constraint pk_hoge;

表が変更されました。

SCOTT> alter table hoge drop constraint uk_hoge;

表が変更されました。

SCOTT> select constraint_name,constraint_type,index_name,table_name from user_constraints where table_name='HOGE' and constraint_type in ('P','U');

レコードが選択されませんでした。

・では、a列、b列に非ユニーク索引を作成しておきます。この非ユニーク索引によってa列に主キー制約の実施に新たな索引は不要。（強制的に作成することもできますが何も指定しなければ制約を実施することが可能な索引があればそちらが利用されます。）
b列は作成した非ユニーク索引の第２列であるため一意制約が実施できず新たな一意索引が作成されます。

SCOTT> create index idx_hoge on hoge (a,b);

索引が作成されました。

SCOTT> select index_name,uniqueness,table_name from user_indexes where table_name='HOGE';

INDEX_NAME		       UNIQUENES TABLE_NAME
------------------------------ --------- ------------------------------
IDX_HOGE		       NONUNIQUE HOGE

SCOTT> alter table hoge add constraint pk_hoge primary key (a);

表が変更されました。

SCOTT> alter table hoge add constraint uk_hoge unique (b);

表が変更されました。

SCOTT> select constraint_name,constraint_type,index_name,table_name from user_constraints where table_name='HOGE' and constraint_type in ('P','U');

CONSTRAINT_NAME 	       C INDEX_NAME		      TABLE_NAME
------------------------------ - ------------------------------ ------------------------------
PK_HOGE 		       P IDX_HOGE 		        HOGE
UK_HOGE 		       U UK_HOGE			HOGE

・主キー制約だけ削除してみてもあらかじめ作成しておいた非ユニーク索引はそのまま。:)

SCOTT> alter table hoge drop constraint pk_hoge;

表が変更されました。

SCOTT> select constraint_name,constraint_type,index_name,table_name from user_constraints where table_name='HOGE' and constraint_type in ('P','U');

CONSTRAINT_NAME 	       C INDEX_NAME		      TABLE_NAME
------------------------------ - ------------------------------ ------------------------------
UK_HOGE 		       U UK_HOGE			HOGE

SCOTT> select index_name,uniqueness,table_name from user_indexes where table_name='HOGE';

INDEX_NAME		       UNIQUENES TABLE_NAME
------------------------------ --------- ------------------------------
IDX_HOGE		       NONUNIQUE HOGE
UK_HOGE 		       UNIQUE	 HOGE

・こんどは、あらかじめ作成しておいた非ユニーク索引を利用せず、主キー制約を実施するためのユニーク索引を強制的に作成してみます。もし表が大きなものであれば索引の作成にそれなりのコストがかかりますよね。

SCOTT> alter table hoge add constraint pk_hoge primary key(a) using index (create unique index pk_hoge_idx on hoge(a));

表が変更されました。

SCOTT> select constraint_name,constraint_type,index_name,table_name from user_constraints where table_name='HOGE' and constraint_type in ('P','U');

CONSTRAINT_NAME 	       C INDEX_NAME			TABLE_NAME
------------------------------ - ------------------------------ ------------------------------
PK_HOGE 		       P PK_HOGE_IDX			HOGE
UK_HOGE 		       U UK_HOGE			HOGE

SCOTT> select index_name,uniqueness,table_name from user_indexes where table_name='HOGE';

INDEX_NAME		       UNIQUENES TABLE_NAME
------------------------------ --------- ------------------------------
IDX_HOGE		       NONUNIQUE HOGE
UK_HOGE 		       UNIQUE	 HOGE
PK_HOGE_IDX		       UNIQUE	 HOGE

SCOTT> 

・前述の例では制約に利用できる既存索引は指定せずOracle任せですが、DDL文で該当索引を指定することもできます。ちなみに、制約の実施に利用できない索引を指定するとエラーが返される。

SCOTT> alter table hoge drop constraint pk_hoge;

表が変更されました。

SCOTT> alter table hoge drop constraint uk_hoge;

表が変更されました。

SCOTT> alter table hoge add constraint pk_hoge primary key (a) using index idx_hoge;

表が変更されました。

SCOTT> alter table hoge add constraint uk_hoge unique (b) using index idx_hoge;
alter table hoge add constraint uk_hoge unique (b) using index idx_hoge
*
行1でエラーが発生しました。:
ORA-14196: 指定した索引は制約に使用できません。

alter table add/drop constraint〜以下のシンタックスはここを参照してね。
Oracle® Database SQL Language Reference 11g Release 2 (11.2) - alter_table::=