ここではORACLEデータベースでビットマップ・ファンクション・逆キー・パーティションインデックスの作成する方法を紹介しています。

インデックスの作成や一覧の確方法は↓で紹介しています。
＞＞【ORACLE】インデックス(索引)を作成・変更・削除するSQL
＞＞【ORACLE】インデックスの一覧を確認するSQL

ビットマップインデックス

ビットマップインデックスとは

列がある値に対する有無を、値と01のビットマップで管理しているインデックスです。

例えば従業員表の性別です。
男性を001という値、女性を002という値で表したとき
1行目のレコードが男性の場合、ビットマップインデックスは次のようなイメージになります。
　001:1
　002:0
というように、それぞれの値に対して、0か1かを保持します。

ビットマップインデックスを作成するSQL

ビットマップインデックスは、通常のインデックスと同様にCREATE文を使います。

インデックス名の前にBITMAPキーワードを指定します。

CREATE BITMAP INDEX インデックス名
ON スキーマ名.テーブル名(列名)
[TABLESPACE 表領域名]
;

次のSQLではEMPLOYEEテーブルのGENDER列に対して、ビットマップインデックスを作成しています。

CREATE BITMAP INDEX idx1 ON user1.employee(gender)
TABLESPACE tbs1
;

ファンクションインデックス

ファンクションインデックスとは

ファンクションインデックスとは、値ではなく、何らかの関数を使って処理した結果でインデックスを作ることが出来ます。

例えば、ある列の値をLEFT関数で左から3文字切り取りした値を使って検索したい場合、LEFT関数で切り取った値をインデックスとして事前に登録することが出来ます。

ある列の値を変換した値としてインデックスに保持することが出来ます。

ファンクションインデックスを作成するSQL

ファンクションインデックスは、通常のインデックスと同様にCREATE文を使います。

ファンクションインデックスを作成するSQLの基本構文は次の通りです。

CREATE INDEX インデックス名
ON スキーマ名.テーブル名(関数)
[TABLESPACE 表領域名]
;

次のSQLでは、DEPT_IDの左から3文字を切り取る関数をファンクションインデックスとして作成しました。

このファンクションインデックスは、SELECT文のサンプルのように「LEFT(dept_id,3)」をWHERE句の条件に指定したときに利用されます。

--INDEXのサンプル
CREATE INDEX idx1 ON user1.employee(LEFT(dept_id,3))
TABLESPACE tbs1
;
--SELECT文のサンプル
SELECT * FROM user1.employee WHERE LEFT(dept_id,3) = 'ABC';

逆キー索引

逆キー索引とは

逆キー索引とは、キーの値のバイトを通常とは逆の順番で格納します。

例えば、社員マスタに大量に社員を登録するとき、連続した社員IDを割り振ってINSERTを行ったとします。

どのINSERTも社員マスタの最後尾にデータを登録しようとして、最後尾のあるリーフブロックにアクセスが集中します。最後尾のあるリーフブロックだけに負荷が集中します。

逆キー索引にすることでI/O分散が図れ、パフォーマンスの向上が図れます。

逆キー索引には注意点があります。

バイトの順番を逆に反転させているので、範囲指定をした検索(<、>など)ではインデックス検索されません。

逆キー索引を作成するSQL

逆キー索引は、通常のインデックスと同様にCREATE文を使います。

REVERSE句を指定します。

CREATE INDEX インデックス名
ON スキーマ名.テーブル名(列名)
[TABLESPACE 表領域名]
REVERSE
;

次のSQLではEMPLOYEEテーブルのID列に対して、逆キー索引を作成しています。

CREATE INDEX idx1 ON user1.employee(id)
TABLESPACE tbs1
REVERSE
;

パーティションインデックス

パーティションインデックスとは

パーティションインデックスとは、テーブルとインデックスを同じ項目でパーティション化したものです。

例えば、テーブルが年月を四半期ごとに４～６、７～９、１０～１２、１～３とパーティションしている場合に、インデックスも同じく年月の四半期ごとにパーティション化することが出来ます。

パーティション化することで、パラレルでSQLを実行したときには、非常に高いパフォーマンスが期待出来ます。

パーティションインデックスを作成するSQL

パーティションインデックスは通常のINDEXと同様にCREATE文を使います。

CREATE文の最期にLOCAL句を付けることで、テーブルと同一のパーティションでパーテイションインデックスを作成することが出来ます。

CREATE INDEX idx1 ON user1.sales(sales_date) LOCAL;

パーティションごとに表領域を指定する場合は次のように書きます。

--レンジまたはリストパーティション
CREATE INDEX idx1 ON user1.sales(sales_date)
LOCAL (PARTIOTION p1 TABLESPACE idx1,
       PARTIOTION p2 TABLESPACE idx2,
       PARTIOTION p3 TABLESPACE idx3,
       PARTIOTION p4 TABLESPACE idx4);

--ハッシュパーティション
CREATE INDEX idx1 ON user1.sales(sales_date)
LOCAL STORE IN(idx1,idx2,idx3);

最後に

インデックスの作成や変更、削除には↓の権限が必要です。
　作成：CREATE ANY INDEX
　変更：ALTER ANY INDEX
　削除：DROP ANY INDEX

システム権限の付与の方法については↓で紹介していますので参考にしてください。
＞＞【ORACLE】システム権限を付与・取消するSQL