オンラインの処理でデータをデータベースより取得してダウンロードをする機能を作成しています。
大量データ下でのSQLのパフォーマンスが悪くjavaでの処理を含めて10分以上かかっています。
現状、SQL単体で5分~7分程度かかっているのですが、
1.SQLの記述が悪い
2.そもそもこれくらいかかるもの
という切り分けができないでいます。
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】
SQL
1SELECT 2 * 3FROM 4 ( 5 SELECT 6 t1.pk1, 7 t1.pk2, 8 t1.pk3, 9 t1.pk4 10 FROM 11 テーブルD t1, 12 ( 13 SELECT 14 --ここでpk1、2単位での最大値を取得 15 t2.pk1, 16 t2.pk2, 17 MAX(t2.pk3) 18 FROM 19 テーブルD t2 20 WHERE 21 t2.pk1 = 'XX' AND 22 t2.pk2 = 'YY' AND 23 EXISTS 24 ( 25 SELECT * FROM テーブルE t3 WHERE t3.pk2 = t2.pk2 26 ) 27 AND 28 -- その他条件 29 GROUP BY 30 t2.pk1, 31 t2.pk2 32 ) 33 t4 34 WHERE 35 t1.pk1 = t4.pk1 AND 36 t1.pk2 = t4.pk2 AND 37 t1.pk3 = t4.pk3 38 ) 39 t5, 40 ( 41 SELECT 42 t11.pk1, 43 t11.pk2, 44 t11.pk3, 45 t11.pk4 46 FROM 47 テーブルA t11, 48 ( 49 SELECT 50 --ここでpk1、2単位での最大値を取得 51 t12.pk1, 52 t12.pk2, 53 MAX(t12.pk3) 54 FROM 55 テーブルA t12 56 WHERE 57 t12.pk1 = 'XX' AND 58 t12.pk2 = 'YY' AND 59 -- その他条件 60 GROUP BY 61 t12.pk1, 62 t12.pk2 63 ) 64 t14 65 WHERE 66 t11.pk1 = t14.pk1 AND 67 t11.pk2 = t14.pk2 AND 68 t11.pk3 = t14.pk3 69 ) 70 t15 71WHERE 72 t5.pk1 = t15.pk1 AND 73 t5.pk2 = t15.pk2 AND 74 t5.pk3 = t15.pk3 75 -- pk4はテーブル間で一致しないのでN:Nの組合せになります 76 77・・・以下、似た条件で結合していっています。 78
全てのテーブルの結合した状態ではないですが、実行計画の抜粋です。
実行計画
| 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 |
回答4件
あなたの回答
tips
プレビュー