[OracleDB] インデックス作成の「create index」文とポイントを紹介

2018年6月25日2022年6月4日

Oracle Databaseのインデックスの作成**SQL**とインデックス作成時のポイントを紹介します。

インデックス作成SQLは各種メディアやブログで紹介されており、指定できるパラメータも多くあります。

この記事では初心者向けにインデックスを作成する時の重要なポイントも合わせて紹介します。

※ この記事はB-Treeインデックスについての記載しますので、

その他のインデックスについては別記事で紹介します。

この記事の内容

インデックス作成のSQL「create index」の基本構文
表領域を指定してインデックス作成を作成
不可視索引の有効・無効化、オンライン中のDML操作の有効化・無効化

インデックス作成SQL：create index

create indexの基本構文は↓になります。

create index <ユーザー名>.<インデックス名> on
<ユーザー名>.<表名>(<列名>,....)
[tablespace <表領域>];

表領域を指定：tablespace

インデックスを格納しておく表領域を指定する時には、「tablespace」句を指定します。

create index <ユーザー名>.<インデックス名> on
<ユーザー名>.<表名>(<列名>,....)
tablespace <表領域>;

インデックスは思った以上にデータサイズを必要とするため、格納先である表領域は指定しましょう。

下がサンプルのSQL文になります。

create index test001.tab001_idx on
test001.tab001(col01)
tablespace testtbs;

なお表領域を指定しない場合、ユーザーのデフォルト表領域が指定されます。

下のSQLで確認できます。

col USERNAME for a30
col DEFAULT_TABLESPACE for a30
select USERNAME, DEFAULT_TABLESPACE
from DBA_USERS;

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEST001 TESTTBS

インデックスの利用有無（不可視索引の有効・無効化）：visible / invisible

オプティマイザのインデックス利用有無を指定するのが「visible / invisible」です。

create index <ユーザー名>.<インデックス名> on
<ユーザー名>.<表名>(<列名>,....)
<visiable or invisible>;

デフォルトでは。「visiable」を設定されます。

通常インデックスを問い合わせ時に使用しますが、「invisible」を指定しますと問合せ時にオプティマイザによって使用されません。

初期化パラメータ「OPTIMIZER_USE_INVISIBLE_INDEXES」をセッションまたはシステムレベルで明示的に「TRUE」に設定することで、

「invisible」を指定したインデックスも使用されます。

使いどころとして、日中帯はインデックスを使用せず夜間のパッチ処理などがあります。

下がサンプルのSQLになります。

create index test001.tab001_idx on
test001.tab001(col01)
invisible;

オンライン中のDML操作の有効化：online

オンライン中のDMLを有効化するのは、「online」句を指定します。

create index <ユーザー名>.<インデックス名> on
<ユーザー名>.<表名>(<列名>,....)
online;

問い合わせ中などはインデックスを使用することはできませんが、

「online」句を使用すると索引の作成中のテーブルへの問い合わせを許可できます。

下がサンプルのSQL文になります。

create index test001.tab001_idx on
test001.tab001(col01)
online;

なおインデックス作成のオンライン中有効化には以下の制約のあるので注意が必要です。

オンライン索引の作成中は、パラレルDMLはサポートされません。ONLINEを指定し、パラレルDML文を発行すると、Oracle Databaseはエラーを戻します。

ビットマップ索引またはクラスタ索引には、ONLINEを指定できません。

UROWID列の従来索引には、ONLINEを指定できません。

索引構成表の一意でない2次索引の場合、索引構成表内の索引キー列の数と論理ROWIDの主キー列の数の合計は、32以下にする必要があります。論理ROWIDは、索引キーに含まれる列を除外します。
マニュアル参照