teratail
回答率
85.48%
質問するログイン新規登録
トップに関する質問SQLのパフォーマンスについて

編集履歴

質問編集履歴

3

実行計画にNameを追加

2017/10/10 03:19

投稿

msd
msd

スコア95

test CHANGED
File without changes
test CHANGED
@@ -14,6 +14,8 @@
14
14
 
15
15
  という切り分けができないでいます。
16
16
 
17
+ DBMSはoracle 11gです
18
+
17
19
 
18
20
 
19
21
  【仕様】
@@ -40,6 +42,8 @@
40
42
 
41
43
  　テーブルE ：300,000件（会員単位の属性情報のデータ）
42
44
 
45
+ 　テーブルE´：300,000件（会員単位の属性情報のデータ。EかE´のどちらかに存在）
46
+
43
47
  　テーブルF ：450件
44
48
 
45
49
  　テーブルG ：450件
@@ -216,72 +220,74 @@
216
220
 
217
221
  実行計画
218
222
 
219
- | Id | Operation || Rows | Bytes | Cost (%CPU)| Time |
220
-
221
- | 0 | SELECT STATEMENT || 1 | 216 | 6894 (1)| 00:01:23 |
222
-
223
- |* 1 | FILTER || | | | |
224
-
225
- |* 2 | HASH JOIN || 94 | 20304 | 6706 (1)| 00:01:21 |
226
-
227
- |* 3 | HASH JOIN || 282 | 53580 | 6701 (1)| 00:01:21 |
228
-
229
- | 4 | VIEW || 1 | 26 | 9 (23)| 00:00:01 |
230
-
231
- | 5 | HASH GROUP BY || 1 | 92 | 9 (23)| 00:00:01 |
232
-
233
- | 6 | NESTED LOOPS || 1 | 92 | 8 (13)| 00:00:01 |
234
-
235
- | 7 | NESTED LOOPS || 1 | 92 | 8 (13)| 00:00:01 |
236
-
237
- | 8 | VIEW || 1 | 36 | 5 (20)| 00:00:01 |
238
-
239
- | 9 | HASH GROUP BY || 1 | 77 | 5 (20)| 00:00:01 |
240
-
241
- |* 10 | TABLE ACCESS BY INDEX ROWID|| 1 | 77 | 4 (0)| 00:00:01 |
242
-
243
- |* 11 | INDEX RANGE SCAN || 13 | | 3 (0)| 00:00:01 |
244
-
245
- |* 12 | INDEX RANGE SCAN || 1 | | 2 (0)| 00:00:01 |
246
-
247
- | 13 | TABLE ACCESS BY INDEX ROWID || 1 | 56 | 3 (0)| 00:00:01 |
248
-
249
- | 14 | NESTED LOOPS || 14652 | 2346K| 6692 (1)| 00:01:21 |
250
-
251
- | 15 | NESTED LOOPS || 35317 | 2346K| 6692 (1)| 00:01:21 |
252
-
253
- | 16 | NESTED LOOPS || 1 | 75 | 1 (0)| 00:00:01 |
254
-
255
- | 17 | NESTED LOOPS || 1 | 47 | 1 (0)| 00:00:01 |
256
-
257
- | 18 | VIEW || 1 | 19 | 1 (0)| 00:00:01 |
258
-
259
- | 19 | HASH GROUP BY || 1 | 95 | 1 (0)| 00:00:01 |
260
-
261
- |* 20 | TABLE ACCESS BY INDEX ROWID|| 1 | 95 | 1 (0)| 00:00:01 |
262
-
263
- |* 21 | INDEX RANGE SCAN || 1 | | 1 (0)| 00:00:01 |
264
-
265
- |* 22 | INDEX UNIQUE SCAN || 1 | 28 | 0 (0)| 00:00:01 |
266
-
267
- |* 23 | INDEX UNIQUE SCAN || 1 | 28 | 0 (0)| 00:00:01 |
268
-
269
- |* 24 | INDEX RANGE SCAN || 35317 | | 6691 (1)| 00:01:21 |
270
-
271
- | 25 | TABLE ACCESS BY INDEX ROWID || 73583 | 6395K| 6691 (1)| 00:01:21 |
272
-
273
- | 26 | VIEW || 1030K| 25M| 1 (0)| 00:00:01 |
274
-
275
- | 27 | HASH GROUP BY || 1030K| 74M| 1 (0)| 00:00:01 |
276
-
277
- |* 28 | TABLE ACCESS BY INDEX ROWID || 1030K| 74M| 1 (0)| 00:00:01 |
278
-
279
- |* 29 | INDEX RANGE SCAN || 55 | | 1 (0)| 00:00:01 |
280
-
281
- |* 30 | TABLE ACCESS BY INDEX ROWID || 183 | 15555 | 4 (0)| 00:00:01 |
282
-
283
- |* 31 | INDEX RANGE SCAN || 1 | | 3 (0)| 00:00:01 |
284
-
285
- |* 32 | TABLE ACCESS BY INDEX ROWID || 1 | 78 | 1 (0)| 00:00:01 |
286
-
287
- |* 33 | INDEX RANGE SCAN || 1 | | 1 (0)| 00:00:01 |
223
+
224
+
225
+ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
226
+
227
+ | 0 | SELECT STATEMENT | | 1 | 216 | 6894 (1)| 00:01:23 |
228
+
229
+ |* 1 | FILTER | | | | | |
230
+
231
+ |* 2 | HASH JOIN | | 94 | 20304 | 6706 (1)| 00:01:21 |
232
+
233
+ |* 3 | HASH JOIN | | 282 | 53580 | 6701 (1)| 00:01:21 |
234
+
235
+ | 4 | VIEW | | 1 | 26 | 9 (23)| 00:00:01 |
236
+
237
+ | 5 | HASH GROUP BY | | 1 | 92 | 9 (23)| 00:00:01 |
238
+
239
+ | 6 | NESTED LOOPS | | 1 | 92 | 8 (13)| 00:00:01 |
240
+
241
+ | 7 | NESTED LOOPS | | 1 | 92 | 8 (13)| 00:00:01 |
242
+
243
+ | 8 | VIEW | | 1 | 36 | 5 (20)| 00:00:01 |
244
+
245
+ | 9 | HASH GROUP BY | | 1 | 77 | 5 (20)| 00:00:01 |
246
+
247
+ |* 10 | TABLE ACCESS BY INDEX ROWID| テーブルC | 1 | 77 | 4 (0)| 00:00:01 |
248
+
249
+ |* 11 | INDEX RANGE SCAN | PK_テーブルC | 13 | | 3 (0)| 00:00:01 |
250
+
251
+ |* 12 | INDEX RANGE SCAN | PK_テーブルC | 1 | | 2 (0)| 00:00:01 |
252
+
253
+ | 13 | TABLE ACCESS BY INDEX ROWID | テーブルC | 1 | 56 | 3 (0)| 00:00:01 |
254
+
255
+ | 14 | NESTED LOOPS | | 14652 | 2346K| 6692 (1)| 00:01:21 |
256
+
257
+ | 15 | NESTED LOOPS | | 35317 | 2346K| 6692 (1)| 00:01:21 |
258
+
259
+ | 16 | NESTED LOOPS | | 1 | 75 | 1 (0)| 00:00:01 |
260
+
261
+ | 17 | NESTED LOOPS | | 1 | 47 | 1 (0)| 00:00:01 |
262
+
263
+ | 18 | VIEW | | 1 | 19 | 1 (0)| 00:00:01 |
264
+
265
+ | 19 | HASH GROUP BY | | 1 | 95 | 1 (0)| 00:00:01 |
266
+
267
+ |* 20 | TABLE ACCESS BY INDEX ROWID| テーブルA　　　 | 1 | 95 | 1 (0)| 00:00:01 |
268
+
269
+ |* 21 | INDEX RANGE SCAN | PK_テーブルA　　　 | 1 | | 1 (0)| 00:00:01 |
270
+
271
+ |* 22 | INDEX UNIQUE SCAN | PK_テーブルA　　　 | 1 | 28 | 0 (0)| 00:00:01 |
272
+
273
+ |* 23 | INDEX UNIQUE SCAN | PK_テーブルA　　　 | 1 | 28 | 0 (0)| 00:00:01 |
274
+
275
+ |* 24 | INDEX RANGE SCAN | PK_テーブルD　　　　 | 35317 | | 6691 (1)| 00:01:21 |
276
+
277
+ | 25 | TABLE ACCESS BY INDEX ROWID | テーブルD　　　　 | 73583 | 6395K| 6691 (1)| 00:01:21 |
278
+
279
+ | 26 | VIEW | | 1030K| 25M| 1 (0)| 00:00:01 |
280
+
281
+ | 27 | HASH GROUP BY | | 1030K| 74M| 1 (0)| 00:00:01 |
282
+
283
+ |* 28 | TABLE ACCESS BY INDEX ROWID | テーブルD　　　　 | 1030K| 74M| 1 (0)| 00:00:01 |
284
+
285
+ |* 29 | INDEX RANGE SCAN | PK_テーブルD　　　　 | 55 | | 1 (0)| 00:00:01 |
286
+
287
+ |* 30 | TABLE ACCESS BY INDEX ROWID | テーブルE　　　　 | 183 | 15555 | 4 (0)| 00:00:01 |
288
+
289
+ |* 31 | INDEX RANGE SCAN | IDX__テーブルE　　　　　 | 1 | | 3 (0)| 00:00:01 |
290
+
291
+ |* 32 | TABLE ACCESS BY INDEX ROWID | テーブルE´　　　　　 | 1 | 78 | 1 (0)| 00:00:01 |
292
+
293
+ |* 33 | INDEX RANGE SCAN | PK_テーブルE´　　　　　 | 1 | | 1 (0)| 00:00:01 |

2

修正

2017/10/10 03:19

投稿

msd
msd

スコア95

test CHANGED
File without changes
test CHANGED
@@ -216,18 +216,8 @@
216
216
 
217
217
  実行計画
218
218
 
219
- ----------------------------------------------------------
220
-
221
- Plan hash value: 2348184404
222
-
223
-
224
-
225
- ---------------------------------------------------------------------------------------
226
-
227
219
  | Id | Operation || Rows | Bytes | Cost (%CPU)| Time |
228
220
 
229
- ---------------------------------------------------------------------------------------
230
-
231
221
  | 0 | SELECT STATEMENT || 1 | 216 | 6894 (1)| 00:01:23 |
232
222
 
233
223
  |* 1 | FILTER || | | | |
@@ -295,5 +285,3 @@
295
285
  |* 32 | TABLE ACCESS BY INDEX ROWID || 1 | 78 | 1 (0)| 00:00:01 |
296
286
 
297
287
  |* 33 | INDEX RANGE SCAN || 1 | | 1 (0)| 00:00:01 |
298
-
299
- ---------------------------------------------------------------------------------------

1

実行計画追加

2017/10/10 02:35

投稿

msd
msd

スコア95

test CHANGED
File without changes
test CHANGED
@@ -209,3 +209,91 @@
209
209
 
210
210
 
211
211
  ```
212
+
213
+
214
+
215
+ 全てのテーブルの結合した状態ではないですが、実行計画の抜粋です。
216
+
217
+ 実行計画
218
+
219
+ ----------------------------------------------------------
220
+
221
+ Plan hash value: 2348184404
222
+
223
+
224
+
225
+ ---------------------------------------------------------------------------------------
226
+
227
+ | Id | Operation || Rows | Bytes | Cost (%CPU)| Time |
228
+
229
+ ---------------------------------------------------------------------------------------
230
+
231
+ | 0 | SELECT STATEMENT || 1 | 216 | 6894 (1)| 00:01:23 |
232
+
233
+ |* 1 | FILTER || | | | |
234
+
235
+ |* 2 | HASH JOIN || 94 | 20304 | 6706 (1)| 00:01:21 |
236
+
237
+ |* 3 | HASH JOIN || 282 | 53580 | 6701 (1)| 00:01:21 |
238
+
239
+ | 4 | VIEW || 1 | 26 | 9 (23)| 00:00:01 |
240
+
241
+ | 5 | HASH GROUP BY || 1 | 92 | 9 (23)| 00:00:01 |
242
+
243
+ | 6 | NESTED LOOPS || 1 | 92 | 8 (13)| 00:00:01 |
244
+
245
+ | 7 | NESTED LOOPS || 1 | 92 | 8 (13)| 00:00:01 |
246
+
247
+ | 8 | VIEW || 1 | 36 | 5 (20)| 00:00:01 |
248
+
249
+ | 9 | HASH GROUP BY || 1 | 77 | 5 (20)| 00:00:01 |
250
+
251
+ |* 10 | TABLE ACCESS BY INDEX ROWID|| 1 | 77 | 4 (0)| 00:00:01 |
252
+
253
+ |* 11 | INDEX RANGE SCAN || 13 | | 3 (0)| 00:00:01 |
254
+
255
+ |* 12 | INDEX RANGE SCAN || 1 | | 2 (0)| 00:00:01 |
256
+
257
+ | 13 | TABLE ACCESS BY INDEX ROWID || 1 | 56 | 3 (0)| 00:00:01 |
258
+
259
+ | 14 | NESTED LOOPS || 14652 | 2346K| 6692 (1)| 00:01:21 |
260
+
261
+ | 15 | NESTED LOOPS || 35317 | 2346K| 6692 (1)| 00:01:21 |
262
+
263
+ | 16 | NESTED LOOPS || 1 | 75 | 1 (0)| 00:00:01 |
264
+
265
+ | 17 | NESTED LOOPS || 1 | 47 | 1 (0)| 00:00:01 |
266
+
267
+ | 18 | VIEW || 1 | 19 | 1 (0)| 00:00:01 |
268
+
269
+ | 19 | HASH GROUP BY || 1 | 95 | 1 (0)| 00:00:01 |
270
+
271
+ |* 20 | TABLE ACCESS BY INDEX ROWID|| 1 | 95 | 1 (0)| 00:00:01 |
272
+
273
+ |* 21 | INDEX RANGE SCAN || 1 | | 1 (0)| 00:00:01 |
274
+
275
+ |* 22 | INDEX UNIQUE SCAN || 1 | 28 | 0 (0)| 00:00:01 |
276
+
277
+ |* 23 | INDEX UNIQUE SCAN || 1 | 28 | 0 (0)| 00:00:01 |
278
+
279
+ |* 24 | INDEX RANGE SCAN || 35317 | | 6691 (1)| 00:01:21 |
280
+
281
+ | 25 | TABLE ACCESS BY INDEX ROWID || 73583 | 6395K| 6691 (1)| 00:01:21 |
282
+
283
+ | 26 | VIEW || 1030K| 25M| 1 (0)| 00:00:01 |
284
+
285
+ | 27 | HASH GROUP BY || 1030K| 74M| 1 (0)| 00:00:01 |
286
+
287
+ |* 28 | TABLE ACCESS BY INDEX ROWID || 1030K| 74M| 1 (0)| 00:00:01 |
288
+
289
+ |* 29 | INDEX RANGE SCAN || 55 | | 1 (0)| 00:00:01 |
290
+
291
+ |* 30 | TABLE ACCESS BY INDEX ROWID || 183 | 15555 | 4 (0)| 00:00:01 |
292
+
293
+ |* 31 | INDEX RANGE SCAN || 1 | | 3 (0)| 00:00:01 |
294
+
295
+ |* 32 | TABLE ACCESS BY INDEX ROWID || 1 | 78 | 1 (0)| 00:00:01 |
296
+
297
+ |* 33 | INDEX RANGE SCAN || 1 | | 1 (0)| 00:00:01 |
298
+
299
+ ---------------------------------------------------------------------------------------