SQLのパフォーマンスについて

Question

オンラインの処理でデータをデータベースより取得してダウンロードをする機能を作成しています。
大量データ下でのSQLのパフォーマンスが悪くjavaでの処理を含めて10分以上かかっています。

現状、SQL単体で5分～7分程度かかっているのですが、
１．SQLの記述が悪い
２．そもそもこれくらいかかるもの

という切り分けができないでいます。
DBMSはoracle 11gです

【仕様】
テーブルA～Eは内部結合
テーブルF～Hは外部結合
各テーブルで特定のカラムの最大を求める必要がある
最終的に、取得したデータ単位内で並べ替えて優先順位1位のデータのみを取得したい

【データ】
テーブルA ：3件
テーブルB ：50件
テーブルC ：150件
テーブルD ：900,000件（会員に関するデータ。会員に対して各世代のデータを持つ。本件においては30万×3世代）
テーブルE ：300,000件（会員単位の属性情報のデータ）
テーブルE´：300,000件（会員単位の属性情報のデータ。EかE´のどちらかに存在）
テーブルF ：450件
テーブルG ：450件
テーブルH ：150件

【SQL】

SELECT
	*
FROM
	(
		SELECT
			t1.pk1,
			t1.pk2,
			t1.pk3,
            t1.pk4
		FROM
			テーブルD t1,
			(
				SELECT
					--ここでpk1、2単位での最大値を取得
					t2.pk1,
					t2.pk2,
					MAX(t2.pk3)
				FROM
					テーブルD t2
				WHERE
					t2.pk1 = 'XX' AND
					t2.pk2 = 'YY' AND
					EXISTS
					(
						SELECT * FROM テーブルE t3 WHERE t3.pk2 = t2.pk2
					)
				AND
					-- その他条件
				GROUP BY
					t2.pk1,
					t2.pk2
			)
			t4
		WHERE
			t1.pk1 = t4.pk1 AND
			t1.pk2 = t4.pk2 AND
			t1.pk3 = t4.pk3
	)
	t5,
	(
		SELECT
			t11.pk1,
			t11.pk2,
			t11.pk3,
            t11.pk4
		FROM
			テーブルA t11,
			(
				SELECT
					--ここでpk1、2単位での最大値を取得
					t12.pk1,
					t12.pk2,
					MAX(t12.pk3)
				FROM
					テーブルA t12
				WHERE
					t12.pk1 = 'XX' AND
					t12.pk2 = 'YY' AND
					-- その他条件
				GROUP BY
					t12.pk1,
					t12.pk2
			)
			t14
		WHERE
			t11.pk1 = t14.pk1 AND
			t11.pk2 = t14.pk2 AND
			t11.pk3 = t14.pk3
	)
	t15
WHERE
	t5.pk1 = t15.pk1 AND
	t5.pk2 = t15.pk2 AND
	t5.pk3 = t15.pk3
    -- pk4はテーブル間で一致しないのでN:Nの組合せになります

･･･以下、似た条件で結合していっています。    

全てのテーブルの結合した状態ではないですが、実行計画の抜粋です。
実行計画

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 216 | 6894 (1)| 00:01:23 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 94 | 20304 | 6706 (1)| 00:01:21 |
|* 3 | HASH JOIN | | 282 | 53580 | 6701 (1)| 00:01:21 |
| 4 | VIEW | | 1 | 26 | 9 (23)| 00:00:01 |
| 5 | HASH GROUP BY | | 1 | 92 | 9 (23)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 92 | 8 (13)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 92 | 8 (13)| 00:00:01 |
| 8 | VIEW | | 1 | 36 | 5 (20)| 00:00:01 |
| 9 | HASH GROUP BY | | 1 | 77 | 5 (20)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID| テーブルC | 1 | 77 | 4 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | PK_テーブルC | 13 | | 3 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | PK_テーブルC | 1 | | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | テーブルC | 1 | 56 | 3 (0)| 00:00:01 |
| 14 | NESTED LOOPS | | 14652 | 2346K| 6692 (1)| 00:01:21 |
| 15 | NESTED LOOPS | | 35317 | 2346K| 6692 (1)| 00:01:21 |
| 16 | NESTED LOOPS | | 1 | 75 | 1 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 1 | 47 | 1 (0)| 00:00:01 |
| 18 | VIEW | | 1 | 19 | 1 (0)| 00:00:01 |
| 19 | HASH GROUP BY | | 1 | 95 | 1 (0)| 00:00:01 |
|* 20 | TABLE ACCESS BY INDEX ROWID| テーブルA　　　 | 1 | 95 | 1 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | PK_テーブルA　　　 | 1 | | 1 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | PK_テーブルA　　　 | 1 | 28 | 0 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | PK_テーブルA　　　 | 1 | 28 | 0 (0)| 00:00:01 |
|* 24 | INDEX RANGE SCAN | PK_テーブルD　　　　 | 35317 | | 6691 (1)| 00:01:21 |
| 25 | TABLE ACCESS BY INDEX ROWID | テーブルD　　　　 | 73583 | 6395K| 6691 (1)| 00:01:21 |
| 26 | VIEW | | 1030K| 25M| 1 (0)| 00:00:01 |
| 27 | HASH GROUP BY | | 1030K| 74M| 1 (0)| 00:00:01 |
|* 28 | TABLE ACCESS BY INDEX ROWID | テーブルD　　　　 | 1030K| 74M| 1 (0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | PK_テーブルD　　　　 | 55 | | 1 (0)| 00:00:01 |
|* 30 | TABLE ACCESS BY INDEX ROWID | テーブルE　　　　 | 183 | 15555 | 4 (0)| 00:00:01 |
|* 31 | INDEX RANGE SCAN | IDX__テーブルE　　　　　 | 1 | | 3 (0)| 00:00:01 |
|* 32 | TABLE ACCESS BY INDEX ROWID | テーブルE´　　　　　 | 1 | 78 | 1 (0)| 00:00:01 |
|* 33 | INDEX RANGE SCAN | PK_テーブルE´　　　　　 | 1 | | 1 (0)| 00:00:01 |

Comments

[sazi]

実行計画のnameを省略されるとちょっと厳しいですね。DBMSはoracleですよね？質問に明記して下さい。

[msd]

nameがないと厳しかったのですね。追加しました。見づらいですが、コピーしてテキストに貼り付けると整って見えます。

[szk.]

t4とt5と最終的な結果の想定件数を教えてください。またt15は3件以下になりますでしょうか。

[SVC34]

サブクエリの中で求めているMAXが上位のクエリに現れていませんが、実際は上位のクエリでこれを「最終的に、取得したデータ単位内で並べ替えて優先順位1位のデータのみを取得したい」という要件のために利用しているのでしょうか。

Answer 1

分析関数 ROW_NUMBER の使用例を使うことで同じテーブルを2度SELECTしないで済むので、件数の多いテーブルに対してはかなりパフォーマンスを改善できます。

会員単位の属性情報のデータ。EかE´のどちらかに存在）

というテーブル設計ではSELECT時のパフォーマンスが悪いので、EとE´を合体したテーブルを作られては？

件数が多いテーブル同士のJOINはNESTED LOOP JOIN より HASH JOIN を使った方がパフォーマンスが改善されることがあります。USE_HASHヒント

また、SELECT文を実行できる範囲のCREATE TABLE, CREATE INDEXは提示された方が解決が早いです。

Answer 2

修正

テーブルA,Dに対して「その他条件」として割愛されています。
テーブルDに対してインデックスは使用されていますが、ROWIDで実テーブルを参照していますので、
その部分でコストが掛かっていると思われます。
多分インデックスに無い項目に対する条件となっていると思われますので、
その条件も明らかにしてもらえますか。

追記

手っ取り早くは、「その他条件」となっているところをコメントアウトして、実行計画を確認してみることです。
それで、性能が改善されるようであれば、SQLを組み直したり、インデックスを追加するなどの手当てが必要になります。
それ以外では、自己結合が多いので、Lead() over()などを使用することで改善されるかもしれません。

Comments

[sazi]

nameが追加される前に勘で回答しちゃいました。テーブルDの方かな？
追記してみます。

[msd]

少し遅くなりました。
実行計画の抜粋です。

| 13 | VIEW | | 1 | 36 | 5 (20)| 00:00:01 |
| 14 | HASH GROUP BY | | 1 | 77 | 5 (20)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID| テーブルA　　　 | 1 | 77 | 4 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | PK_テーブルA　　　 | 13 | | 3 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | PK_テーブルA　　　 | 1 | | 2 (0)| 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID | テーブルA　　　 | 1 | 56 | 3 (0)| 00:00:01 |
|* 19 | HASH JOIN | | 71091 | 8122K| 6696 (1)| 00:01:21 |
|* 20 | INDEX RANGE SCAN | PK_テーブルA　　　 | 2 | 56 | 1 (0)| 00:00:01 |
| 21 | TABLE ACCESS BY INDEX ROWID | テーブルD　　　　 | 1030K| 87M| 6691 (1)| 00:01:21 |

[msd]

アドバイスありがとうございます。
再検討してみます。

Answer 3

勘で申し訳ありませんが、テーブルDとテーブルEとのEXISTSをJOINに変えてみたらどうでしょうか？
group byされているので同値になると思われます。

        SELECT
            t1.pk1,
            t1.pk2,
            t1.pk3,
            t1.pk4
        FROM
            テーブルD t1,
            (
                SELECT
                    --ここでpk1、2単位での最大値を取得
                    t2.pk1,
                    t2.pk2,
                    MAX(t2.pk3) pk3
                FROM
                    テーブルD t2,
                    テーブルE t3
                WHERE
                    t3.pk2 = t2.pk2 AND
                    t2.pk1 = 'XX' AND
                    t2.pk2 = 'YY'
                -- AND
                    -- その他条件
                GROUP BY
                    t2.pk1,
                    t2.pk2
            )
            t4
        WHERE
            t1.pk1 = t4.pk1 AND
            t1.pk2 = t4.pk2 AND
            t1.pk3 = t4.pk3

Comments

[msd]

当初はjoinしていました。
existsのほうを試したのが上の結果ですが、あまり変わっていないのが現状です。

Answer 4

利用されているRDBMSが不明ですが、実行計画を取得してどこに時間がかかっているかを確認されるとよいかと思います。

一般的には、実行されるSQLの冒頭にEXPLAINを付与すれば取得できることが多いかと思います。

Comments

[msd]

実行計画を追加しました。