クラスタ索引を使ってみた

クラスタ索引とは、簡単に言うと頻繁に結合するテーブル同士の結合条件Columnを一カ所に集中させて
ディスクのランダムアクセスを減らそう（シーケンシャルアクセスにする）っていうものかな
詳細は下記へ
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19207-02/data_acc.htm#801641

ともかく一カ所にまとまってたら早くなるってイメージで良いです
以前触ったことはあったけどあんま使い方覚えてないので再度挑戦

まずはクラスタ作成

CREATE CLUSTER MST_TRAN
(
    MST_ID                         NUMBER
)

次にクラスタを使用するテーブルを作成
MSTとTRANテーブルをこんな感じで

CREATE TABLE MST
(
    MST_ID                         NUMBER NOT NULL,
    NAME                           VARCHAR2(20),
    CONSTRAINT PK_MST_ID PRIMARY KEY (MST_ID) USING INDEX
)
CLUSTER MST_TRAN (MST_ID)

CREATE TABLE TRAN
(
    TRAN_ID                        NUMBER NOT NULL,
    NAME                           VARCHAR2(10),
    MST_ID                         NUMBER,
    CONSTRAINT PK_TRAN_ID PRIMARY KEY (TRAN_ID) USING INDEX
)
CLUSTER MST_TRAN (MST_ID)

もう一つ、同じテーブル構成でクラスタを使用しないMST2,TRAN2を作成
A. MST,TRAN = ClusterIndex(MST_ID)
B. MST2,TRAN = クラスタなしFKのみ

んでテストデータとして
MST,MST2 = 1,000rec
TRAN,TRAN2 = 100,000rec

SELECT
	 M.*
	,T.*
FROM
	MST M
INNER JOIN
	TRAN T
ON
	T.MST_ID = M.MST_ID

SELECT
	 M.*
	,T.*
FROM
	MST2 M
INNER JOIN
	TRAN2 T
ON
	T.MST_ID = M.MST_ID

結果
A.

実行計画
- Cost:2
- Byte:58
- Join:NESTED LOOPS
AutoTrace
- consistent gets:13004

実行計画
- Cost:111
- Byte:6103862
- Join:HASH JOIN
AutoTrace
- consistent gets:10349

とりあえず実行計画だけでみると圧倒的にクラスタ索引が強いが
どうも実行計画を見ると勝手に最適化されているようなので
NestedLoop　と　HashJoin　で別々に実行計画とってみることにする。

ヒント句で NestedLoop
A.

SELECT
/*+USE_NL(M,T)*/
	 M.*
	,T.*
FROM
	MST M
INNER JOIN
	TRAN T
ON
	T.MST_ID = M.MST_ID

SELECT
/*+USE_NL(M,T)*/
	 M.*
	,T.*
FROM
	MST2 M
INNER JOIN
	TRAN2 T
ON
	T.MST_ID = M.MST_ID

結果
A.

実行計画
- Cost:2
- Byte:58
- join:NESTED LOOPS
AutoTrace
- consistent gets:13004

実行計画
- Cost:1067
- Byte:6103862
- Join:NESTED LOOPS
AutoTrace
- consistent gets:10349

ヒント句で HashJoin
A.

SELECT
/*+USE_HASH(M,T)*/
	 M.*
	,T.*
FROM
	MST M
INNER JOIN
	TRAN T
ON
	T.MST_ID = M.MST_ID

SELECT
/*+USE_HASH(M,T)*/
	 M.*
	,T.*
FROM
	MST2 M
INNER JOIN
	TRAN2 T
ON
	T.MST_ID = M.MST_ID

実行計画
- Cost:5
- Byte:58
- join:HASH JOIN
AutoTrace
- consistent gets:11910

実行計画
- Cost:111
- Byte:6103862
- HASH:JOIN
AutoTrace
- consistent gets:10349

SortMergeもためしてみた
A.

SELECT
/*+USE_MERGE(m,t)*/
	 M.*
	,T.*
FROM
	MST M
INNER JOIN
	TRAN T
ON
	T.MST_ID = M.MST_ID

SELECT
/*+USE_MERGE(m,t)*/
	 M.*
	,T.*
FROM
	MST2 M
INNER JOIN
	TRAN2 T
ON
	T.MST_ID = M.MST_ID

実行計画
- Cost:3
- Byte:58
- Join:SORT MERGE
AutoTrace
- consistent gets:4006

実行計画
- Cost:1063
- Byte:6103862
- Join:SORT MERGE
AutoTrace
- consistent gets:457

上記の事を試して気づいたのだがクラスタ索引はHashJoinでは使われない
気になってマニュアル読んでたら別途ハッシュクラスタってのがあるみたい
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19224-02/hash.htm#14273

ちなみにこの件数では体感はどちらでも変わりませんでした。
ただ以前試した際には、クラスタなしは件数が増加するに従ってパフォーマンスの低下が激しかったけど
クラスタでは件数の増加してもそこまでパフォーマンスが落ちなかった記憶があるので
1000万とか1億とかのレコードをガシガシJoinするのであれば有効だと思いますよ