スキーマ定義

[ナレッジに戻る]

1. スキーマ定義

1.1 データベースの作成

1.1.1 データベース名

1.2 表の作成

1.2.1 表名
1.2.2 表制約
1.2.3 ROWID

1.3 列定義

1.3.1 列名
1.3.2 型
1.3.3 ARRAY
1.3.4 DEFAULT指定
1.3.5 IDENTITY指定
1.3.6 列制約
1.3.7 hint

1.4 LOBについて

1.5 索引の作成

1.5.1 索引タイプ
1.5.2 索引名
1.5.3 キー
1.5.4 hint

2. スキーマ定義のコツ

2.1 表定義のコツ

2.1.1 hintの効果的な指定方法

2.2 索引定義のコツ

2.2.1 索引をつけると効果的な例

B+木索引およびビットマップ索引では、キーに使用される列の合計サイズが996以下でなければなりません。各型に対するサイズは以下の式で計算されます。配列型についても同じ計算式になります。
たとえばキーの数がひとつのとき、charおよびvarcharの最大長は994、ncharおよびnvarcharの最大長は497になります。この値を超える型の列にはB+木索引およびビットマップ索引をつけることはできません。

char(N), varchar(N)           ((n+1)/4+1)*4
nchar(N), nvarchar(N)         (n/2+1)*4
int                           4
bigint                        8
float                         8
datetime                      8
language                      16

1.5.4 hint

hintは索引の動作を制御するために指定します。
B+木索引には以下のヒントをつけることができます。複数指定するときは空白で区切ります。

unique          同じ値を持ったキー(複数の場合は同じ値の組み合わせ)がないことを保証する。
'normalized'    文字列に対して簡単なあいまい検索を可能にする(大文字小文字、全角半角の同一視)。

全文索引には以下のヒントをつけることができます。全体を引用符で囲み、複数指定するときはカンマで区切ります。なお、ここではすべてが説明されているわけではありません。全文索引のさらに詳しい作成方法は「全文索引をつかいこなす」を参照してください。

delayed         遅延更新によりデータの更新操作を高速化します。
sectionized     配列型に定義したときに、条件がどの配列要素に合致したのかを取得できるようにします。
inverted=(...)  全文索引を構成する転置ファイルの動作を制御します。

2. スキーマ定義のコツ

2.1 表定義のコツ

2.1.1 hintの効果的な指定方法

表を構成する列値は、原則としてRecordファイルに格納されます。 (BLOBとNCLOBは別のLobファイルに格納されます。) Recordファイルは固定長用のファイルと可変長用のファイルから構成されます。固定長のファイルには、固定長型(ROWID, int, bigint, float, datetime)の列値と可変長ファイルに格納される可変長オブジェクトへのポインターが格納されます。 hint heapつきの列やLOBがあるときは、そのオブジェクトへのポインターも格納されます。また、hint 'fixed'が指定されている列も固定長のファイルに格納されます。可変長のファイルには、可変長型(char, varchar, nchar, nvarchar, binary)の列値をひとまとまりにした可変長オブジェクトが格納されます。以下の例を見てください。

create table TBL (
  id int,
  title nvarchar(100),
  contents nvarchar(no limit)
);

このとき、固定長ファイルには以下のデータが格納されます。

ROWID                   (4byte)
id                      (4byte)
可変長へのポインター    (6byte)

また、可変長ファイルには以下のデータが格納されます。

title                   (最大200byte)
contents                (無制限)

ここで、簡単な検索アプリを考えてみます。 idが指定した範囲にあるデータの一覧を得ることにします。一覧なのでidとtitleがあればいいでしょう。

select id, title from TBL where id between ? and ?;

この処理をするためにはidとtitleの値があればいいのですが、可変長オブジェクトにtitleとcontentsがまとめられているため、不必要なcontentsのデータまで読み込むことになります。このような場合、contentsをヒープにします。

create table TBL (
  id int,
  title nvarchar(100),
  contents nvarchar(no limit) hint heap
);

こうすると、固定長ファイルには以下のデータが格納されます。

ROWID                   (4byte)
id                      (4byte)
contentsへのポインター  (6byte)
可変長へのポインター    (6byte)

可変長ファイルには以下のデータが格納されます。

title                   (最大200byte)

contentsの値は別のRecordファイルに格納されます。
ここで先ほどのSQL文を考えてみます。

select id, title from TBL where id between ? and ?;

こんどはtitleだけが取得されるので、無駄な読み込みがなくなり速度が改善されます。ただし、titleとcontentsを同時に取得することが多い場合には逆効果になることもあります。
さらに、titleを固定長ファイルに格納してしまうことも可能です。

create table TBL (
  id int,
  title nvarchar(100) hint 'fixed',
  contents nvarchar(no limit) hint heap
);

hint 'fixed'のほうは引用符で囲まれていることに注意してください。こうすると、固定長ファイルには以下のデータが格納されます。

ROWID                   (4byte)
id                      (4byte)
title                   (200byte)
contentsへのポインター  (6byte)

このとき可変長ファイルは作られません。
titleに対応する部分は最大長の100文字分=200byteが領域として確保されます。こんどは固定長ファイルしか読まれないので、その分処理は簡単になります。ただし、固定長のデータサイズが著しく増加しています(20⇒214)。このことによりバッファリングの効果が薄れるので、一度に大量のレコードを読み込む場合は逆効果になるおそれがあります。しかし、一般に固定長データと同時に取得されることが分かっている比較的短い可変長データには、hint 'fixed'をつけたほうがいいと予想されます。

2.2 索引定義のコツ

索引を効果的につけると、特定のSELECT文が高速に実行できます。しかし、更新操作は索引ファイルにも操作を行うので、むやみに索引をつけると更新操作が遅くなります。また、選択性の悪い列にB+木索引をつけるなど、データのばらつき度合いと索引タイプが合っていないと、更新操作が遅くなるだけでなく、検索操作も逆に遅くなる場合があります。想定される検索操作とデータのばらつき度合いに応じて、効果的な索引定義を心がけるようにしてください。

2.2.1 索引をつけると効果的な例

長い文字列データが格納され、中間一致のLIKE条件や自然文検索や関連文書検索のためのCONTAINS条件を使用したい

このような場合は、その列に全文索引をつけてください。ただし、データのばらつきがほとんどない列に全文索引をつけると、更新操作だけでなく検索操作にも期待される速度が出ないことがあります。

比較の検索条件に使われることが多く、データのばらつきが大きい

このような場合は、その列にB+木索引をつけると検索操作が速くなることが期待されます。もし同じデータが挿入されることがない列であれば、hint uniqueをつけるとオプティマイザーがそれを情報として利用できるので、さらに効果的です。 PRIMARY KEYやUNIQUE制約の宣言をすると、暗黙の索引が作成されるので同じ効果が得られます。

select * from TBL where f < 10;
-- fの値にばらつきが大きい場合、以下のような索引をつけると速くなる。
create index IDX on TBL(f);

比較の検索条件に使われることが多いが、データのばらつきが少ない データのばらつきが大きい列の比較の検索条件とANDで組み合わされることが多い

このような場合は、ばらつきが大きい列との複合索引にすると速くなることがあります。

select * from TBL where f < 10 and g = 500;
-- fの値にばらつきが大きく、gの値は同じものが多い場合、以下のような索引をつけるとよい。
create index IDX on TBL(f, g);

この複合索引はfだけをキーにした索引としても使用できるので、fだけの索引を重複して定義する必要はありません。

単独で使われることが多い、または全文検索と組み合わされることが多い

このような場合は、ビットマップ索引を使うと速くなることがあります。

select * from TBL where f = 'male' and title like '%senior%';
-- fの値にばらつきがほとんどない場合、以下のような索引をつけると速くなる。
create bitmap index IDX on TBL(f);

ビットマップ索引は、データのばらつきが大きくなると更新操作が著しく遅くなることがあるため、データの異なり数の上限が予測できない列には使用を避けてください。

ソートキーに使われることが多い

B+木索引を用いるとキーの順または逆順にレコードを取得することができます。しかし、B+木索引にはキーがNULLであるようなレコードは格納されません。したがって、B+木索引がついている列がソートキーに使われる場合、以下の条件を満たしたときのみレコードの取得にB+木索引が使用されます。

その列にNOT NULL制約がついている。
その列がNULL値のときにヒットしないような検索条件がついている。
その列が複合索引の第2キーに使用されており、その索引の第1キーにequal条件がついている。

具体的には以下のような場合が該当します。

create table TBL (f int NOT NULL, g int, h int);
create index IDX1 on TBL(f);      -- NOT NULL制約のついた列 f 単独の索引
create index IDX2 on TBL(g);      -- g 単独の索引
create index IDX3 on TBL(h, g);   -- h と g の複合索引
…
select * from TBL order by f;     -- IDX1を用いてfの昇順にレコードを取得する。
select * from TBL where g > 10 order by g;
                                  -- g>10の条件によりNULLが無視できるので、
                                  -- IDX2を用いてgの昇順にレコードを取得する。
select * from TBL where h = 100 order by g;
                                  -- IDX3を用いてh=100を満たすレコードをgの昇順に取得する。

create all rows indexを用いると、NULL値を持つ列についてもソートキーとして索引が使用されます。