質問編集履歴
3
実行計画にNameを追加
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -6,6 +6,7 @@
|
|
|
6
6
|
２．そもそもこれくらいかかるもの
|
|
7
7
|
|
|
8
8
|
という切り分けができないでいます。
|
|
9
|
+
DBMSはoracle 11gです
|
|
9
10
|
|
|
10
11
|
【仕様】
|
|
11
12
|
テーブルA～Eは内部結合
|
|
@@ -19,6 +20,7 @@
|
|
|
19
20
|
テーブルC ：150件
|
|
20
21
|
テーブルD ：900,000件（会員に関するデータ。会員に対して各世代のデータを持つ。本件においては30万×3世代）
|
|
21
22
|
テーブルE ：300,000件（会員単位の属性情報のデータ）
|
|
23
|
+
テーブルE´：300,000件（会員単位の属性情報のデータ。EかE´のどちらかに存在）
|
|
22
24
|
テーブルF ：450件
|
|
23
25
|
テーブルG ：450件
|
|
24
26
|
テーブルH ：150件
|
|
@@ -107,38 +109,39 @@
|
|
|
107
109
|
|
|
108
110
|
全てのテーブルの結合した状態ではないですが、実行計画の抜粋です。
|
|
109
111
|
実行計画
|
|
112
|
+
|
|
110
|
-
| Id | Operation || Rows | Bytes | Cost (%CPU)| Time |
|
|
113
|
+
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
|
|
111
|
-
| 0 | SELECT STATEMENT || 1 | 216 | 6894 (1)| 00:01:23 |
|
|
114
|
+
| 0 | SELECT STATEMENT | | 1 | 216 | 6894 (1)| 00:01:23 |
|
|
112
|
-
|* 1 | FILTER || | | | |
|
|
115
|
+
|* 1 | FILTER | | | | | |
|
|
113
|
-
|* 2 | HASH JOIN || 94 | 20304 | 6706 (1)| 00:01:21 |
|
|
116
|
+
|* 2 | HASH JOIN | | 94 | 20304 | 6706 (1)| 00:01:21 |
|
|
114
|
-
|* 3 | HASH JOIN || 282 | 53580 | 6701 (1)| 00:01:21 |
|
|
117
|
+
|* 3 | HASH JOIN | | 282 | 53580 | 6701 (1)| 00:01:21 |
|
|
115
|
-
| 4 | VIEW || 1 | 26 | 9 (23)| 00:00:01 |
|
|
118
|
+
| 4 | VIEW | | 1 | 26 | 9 (23)| 00:00:01 |
|
|
116
|
-
| 5 | HASH GROUP BY || 1 | 92 | 9 (23)| 00:00:01 |
|
|
119
|
+
| 5 | HASH GROUP BY | | 1 | 92 | 9 (23)| 00:00:01 |
|
|
117
|
-
| 6 | NESTED LOOPS || 1 | 92 | 8 (13)| 00:00:01 |
|
|
120
|
+
| 6 | NESTED LOOPS | | 1 | 92 | 8 (13)| 00:00:01 |
|
|
118
|
-
| 7 | NESTED LOOPS || 1 | 92 | 8 (13)| 00:00:01 |
|
|
121
|
+
| 7 | NESTED LOOPS | | 1 | 92 | 8 (13)| 00:00:01 |
|
|
119
|
-
| 8 | VIEW || 1 | 36 | 5 (20)| 00:00:01 |
|
|
122
|
+
| 8 | VIEW | | 1 | 36 | 5 (20)| 00:00:01 |
|
|
120
|
-
| 9 | HASH GROUP BY || 1 | 77 | 5 (20)| 00:00:01 |
|
|
123
|
+
| 9 | HASH GROUP BY | | 1 | 77 | 5 (20)| 00:00:01 |
|
|
121
|
-
|* 10 | TABLE ACCESS BY INDEX ROWID|| 1 | 77 | 4 (0)| 00:00:01 |
|
|
124
|
+
|* 10 | TABLE ACCESS BY INDEX ROWID| テーブルC | 1 | 77 | 4 (0)| 00:00:01 |
|
|
122
|
-
|* 11 | INDEX RANGE SCAN || 13 | | 3 (0)| 00:00:01 |
|
|
125
|
+
|* 11 | INDEX RANGE SCAN | PK_テーブルC | 13 | | 3 (0)| 00:00:01 |
|
|
123
|
-
|* 12 | INDEX RANGE SCAN || 1 | | 2 (0)| 00:00:01 |
|
|
126
|
+
|* 12 | INDEX RANGE SCAN | PK_テーブルC | 1 | | 2 (0)| 00:00:01 |
|
|
124
|
-
| 13 | TABLE ACCESS BY INDEX ROWID || 1 | 56 | 3 (0)| 00:00:01 |
|
|
127
|
+
| 13 | TABLE ACCESS BY INDEX ROWID | テーブルC | 1 | 56 | 3 (0)| 00:00:01 |
|
|
125
|
-
| 14 | NESTED LOOPS || 14652 | 2346K| 6692 (1)| 00:01:21 |
|
|
128
|
+
| 14 | NESTED LOOPS | | 14652 | 2346K| 6692 (1)| 00:01:21 |
|
|
126
|
-
| 15 | NESTED LOOPS || 35317 | 2346K| 6692 (1)| 00:01:21 |
|
|
129
|
+
| 15 | NESTED LOOPS | | 35317 | 2346K| 6692 (1)| 00:01:21 |
|
|
127
|
-
| 16 | NESTED LOOPS || 1 | 75 | 1 (0)| 00:00:01 |
|
|
130
|
+
| 16 | NESTED LOOPS | | 1 | 75 | 1 (0)| 00:00:01 |
|
|
128
|
-
| 17 | NESTED LOOPS || 1 | 47 | 1 (0)| 00:00:01 |
|
|
131
|
+
| 17 | NESTED LOOPS | | 1 | 47 | 1 (0)| 00:00:01 |
|
|
129
|
-
| 18 | VIEW || 1 | 19 | 1 (0)| 00:00:01 |
|
|
132
|
+
| 18 | VIEW | | 1 | 19 | 1 (0)| 00:00:01 |
|
|
130
|
-
| 19 | HASH GROUP BY || 1 | 95 | 1 (0)| 00:00:01 |
|
|
133
|
+
| 19 | HASH GROUP BY | | 1 | 95 | 1 (0)| 00:00:01 |
|
|
131
|
-
|* 20 | TABLE ACCESS BY INDEX ROWID|| 1 | 95 | 1 (0)| 00:00:01 |
|
|
134
|
+
|* 20 | TABLE ACCESS BY INDEX ROWID| テーブルA | 1 | 95 | 1 (0)| 00:00:01 |
|
|
132
|
-
|* 21 | INDEX RANGE SCAN || 1 | | 1 (0)| 00:00:01 |
|
|
135
|
+
|* 21 | INDEX RANGE SCAN | PK_テーブルA | 1 | | 1 (0)| 00:00:01 |
|
|
133
|
-
|* 22 | INDEX UNIQUE SCAN || 1 | 28 | 0 (0)| 00:00:01 |
|
|
136
|
+
|* 22 | INDEX UNIQUE SCAN | PK_テーブルA | 1 | 28 | 0 (0)| 00:00:01 |
|
|
134
|
-
|* 23 | INDEX UNIQUE SCAN || 1 | 28 | 0 (0)| 00:00:01 |
|
|
137
|
+
|* 23 | INDEX UNIQUE SCAN | PK_テーブルA | 1 | 28 | 0 (0)| 00:00:01 |
|
|
135
|
-
|* 24 | INDEX RANGE SCAN || 35317 | | 6691 (1)| 00:01:21 |
|
|
138
|
+
|* 24 | INDEX RANGE SCAN | PK_テーブルD | 35317 | | 6691 (1)| 00:01:21 |
|
|
136
|
-
| 25 | TABLE ACCESS BY INDEX ROWID || 73583 | 6395K| 6691 (1)| 00:01:21 |
|
|
139
|
+
| 25 | TABLE ACCESS BY INDEX ROWID | テーブルD | 73583 | 6395K| 6691 (1)| 00:01:21 |
|
|
137
|
-
| 26 | VIEW || 1030K| 25M| 1 (0)| 00:00:01 |
|
|
140
|
+
| 26 | VIEW | | 1030K| 25M| 1 (0)| 00:00:01 |
|
|
138
|
-
| 27 | HASH GROUP BY || 1030K| 74M| 1 (0)| 00:00:01 |
|
|
141
|
+
| 27 | HASH GROUP BY | | 1030K| 74M| 1 (0)| 00:00:01 |
|
|
139
|
-
|* 28 | TABLE ACCESS BY INDEX ROWID || 1030K| 74M| 1 (0)| 00:00:01 |
|
|
142
|
+
|* 28 | TABLE ACCESS BY INDEX ROWID | テーブルD | 1030K| 74M| 1 (0)| 00:00:01 |
|
|
140
|
-
|* 29 | INDEX RANGE SCAN || 55 | | 1 (0)| 00:00:01 |
|
|
143
|
+
|* 29 | INDEX RANGE SCAN | PK_テーブルD | 55 | | 1 (0)| 00:00:01 |
|
|
141
|
-
|* 30 | TABLE ACCESS BY INDEX ROWID || 183 | 15555 | 4 (0)| 00:00:01 |
|
|
144
|
+
|* 30 | TABLE ACCESS BY INDEX ROWID | テーブルE | 183 | 15555 | 4 (0)| 00:00:01 |
|
|
142
|
-
|* 31 | INDEX RANGE SCAN || 1 | | 3 (0)| 00:00:01 |
|
|
145
|
+
|* 31 | INDEX RANGE SCAN | IDX__テーブルE | 1 | | 3 (0)| 00:00:01 |
|
|
143
|
-
|* 32 | TABLE ACCESS BY INDEX ROWID || 1 | 78 | 1 (0)| 00:00:01 |
|
|
146
|
+
|* 32 | TABLE ACCESS BY INDEX ROWID | テーブルE´ | 1 | 78 | 1 (0)| 00:00:01 |
|
|
144
|
-
|* 33 | INDEX RANGE SCAN || 1 | | 1 (0)| 00:00:01 |
|
|
147
|
+
|* 33 | INDEX RANGE SCAN | PK_テーブルE´ | 1 | | 1 (0)| 00:00:01 |
|
2
修正
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -107,12 +107,7 @@
|
|
|
107
107
|
|
|
108
108
|
全てのテーブルの結合した状態ではないですが、実行計画の抜粋です。
|
|
109
109
|
実行計画
|
|
110
|
-
----------------------------------------------------------
|
|
111
|
-
Plan hash value: 2348184404
|
|
112
|
-
|
|
113
|
-
---------------------------------------------------------------------------------------
|
|
114
110
|
| Id | Operation || Rows | Bytes | Cost (%CPU)| Time |
|
|
115
|
-
---------------------------------------------------------------------------------------
|
|
116
111
|
| 0 | SELECT STATEMENT || 1 | 216 | 6894 (1)| 00:01:23 |
|
|
117
112
|
|* 1 | FILTER || | | | |
|
|
118
113
|
|* 2 | HASH JOIN || 94 | 20304 | 6706 (1)| 00:01:21 |
|
|
@@ -146,5 +141,4 @@
|
|
|
146
141
|
|* 30 | TABLE ACCESS BY INDEX ROWID || 183 | 15555 | 4 (0)| 00:00:01 |
|
|
147
142
|
|* 31 | INDEX RANGE SCAN || 1 | | 3 (0)| 00:00:01 |
|
|
148
143
|
|* 32 | TABLE ACCESS BY INDEX ROWID || 1 | 78 | 1 (0)| 00:00:01 |
|
|
149
|
-
|* 33 | INDEX RANGE SCAN || 1 | | 1 (0)| 00:00:01 |
|
|
144
|
+
|* 33 | INDEX RANGE SCAN || 1 | | 1 (0)| 00:00:01 |
|
|
150
|
-
---------------------------------------------------------------------------------------
|
1
実行計画追加
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -103,4 +103,48 @@
|
|
|
103
103
|
|
|
104
104
|
･･･以下、似た条件で結合していっています。
|
|
105
105
|
|
|
106
|
-
```
|
|
106
|
+
```
|
|
107
|
+
|
|
108
|
+
全てのテーブルの結合した状態ではないですが、実行計画の抜粋です。
|
|
109
|
+
実行計画
|
|
110
|
+
----------------------------------------------------------
|
|
111
|
+
Plan hash value: 2348184404
|
|
112
|
+
|
|
113
|
+
---------------------------------------------------------------------------------------
|
|
114
|
+
| Id | Operation || Rows | Bytes | Cost (%CPU)| Time |
|
|
115
|
+
---------------------------------------------------------------------------------------
|
|
116
|
+
| 0 | SELECT STATEMENT || 1 | 216 | 6894 (1)| 00:01:23 |
|
|
117
|
+
|* 1 | FILTER || | | | |
|
|
118
|
+
|* 2 | HASH JOIN || 94 | 20304 | 6706 (1)| 00:01:21 |
|
|
119
|
+
|* 3 | HASH JOIN || 282 | 53580 | 6701 (1)| 00:01:21 |
|
|
120
|
+
| 4 | VIEW || 1 | 26 | 9 (23)| 00:00:01 |
|
|
121
|
+
| 5 | HASH GROUP BY || 1 | 92 | 9 (23)| 00:00:01 |
|
|
122
|
+
| 6 | NESTED LOOPS || 1 | 92 | 8 (13)| 00:00:01 |
|
|
123
|
+
| 7 | NESTED LOOPS || 1 | 92 | 8 (13)| 00:00:01 |
|
|
124
|
+
| 8 | VIEW || 1 | 36 | 5 (20)| 00:00:01 |
|
|
125
|
+
| 9 | HASH GROUP BY || 1 | 77 | 5 (20)| 00:00:01 |
|
|
126
|
+
|* 10 | TABLE ACCESS BY INDEX ROWID|| 1 | 77 | 4 (0)| 00:00:01 |
|
|
127
|
+
|* 11 | INDEX RANGE SCAN || 13 | | 3 (0)| 00:00:01 |
|
|
128
|
+
|* 12 | INDEX RANGE SCAN || 1 | | 2 (0)| 00:00:01 |
|
|
129
|
+
| 13 | TABLE ACCESS BY INDEX ROWID || 1 | 56 | 3 (0)| 00:00:01 |
|
|
130
|
+
| 14 | NESTED LOOPS || 14652 | 2346K| 6692 (1)| 00:01:21 |
|
|
131
|
+
| 15 | NESTED LOOPS || 35317 | 2346K| 6692 (1)| 00:01:21 |
|
|
132
|
+
| 16 | NESTED LOOPS || 1 | 75 | 1 (0)| 00:00:01 |
|
|
133
|
+
| 17 | NESTED LOOPS || 1 | 47 | 1 (0)| 00:00:01 |
|
|
134
|
+
| 18 | VIEW || 1 | 19 | 1 (0)| 00:00:01 |
|
|
135
|
+
| 19 | HASH GROUP BY || 1 | 95 | 1 (0)| 00:00:01 |
|
|
136
|
+
|* 20 | TABLE ACCESS BY INDEX ROWID|| 1 | 95 | 1 (0)| 00:00:01 |
|
|
137
|
+
|* 21 | INDEX RANGE SCAN || 1 | | 1 (0)| 00:00:01 |
|
|
138
|
+
|* 22 | INDEX UNIQUE SCAN || 1 | 28 | 0 (0)| 00:00:01 |
|
|
139
|
+
|* 23 | INDEX UNIQUE SCAN || 1 | 28 | 0 (0)| 00:00:01 |
|
|
140
|
+
|* 24 | INDEX RANGE SCAN || 35317 | | 6691 (1)| 00:01:21 |
|
|
141
|
+
| 25 | TABLE ACCESS BY INDEX ROWID || 73583 | 6395K| 6691 (1)| 00:01:21 |
|
|
142
|
+
| 26 | VIEW || 1030K| 25M| 1 (0)| 00:00:01 |
|
|
143
|
+
| 27 | HASH GROUP BY || 1030K| 74M| 1 (0)| 00:00:01 |
|
|
144
|
+
|* 28 | TABLE ACCESS BY INDEX ROWID || 1030K| 74M| 1 (0)| 00:00:01 |
|
|
145
|
+
|* 29 | INDEX RANGE SCAN || 55 | | 1 (0)| 00:00:01 |
|
|
146
|
+
|* 30 | TABLE ACCESS BY INDEX ROWID || 183 | 15555 | 4 (0)| 00:00:01 |
|
|
147
|
+
|* 31 | INDEX RANGE SCAN || 1 | | 3 (0)| 00:00:01 |
|
|
148
|
+
|* 32 | TABLE ACCESS BY INDEX ROWID || 1 | 78 | 1 (0)| 00:00:01 |
|
|
149
|
+
|* 33 | INDEX RANGE SCAN || 1 | | 1 (0)| 00:00:01 |
|
|
150
|
+
---------------------------------------------------------------------------------------
|