teratail header banner
teratail header banner
質問するログイン新規登録

質問編集履歴

3

実行計画にNameを追加

2017/10/10 03:19

投稿

msd
msd

スコア95

title CHANGED
File without changes
body CHANGED
@@ -6,6 +6,7 @@
6
6
  2.そもそもこれくらいかかるもの
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

修正

2017/10/10 03:19

投稿

msd
msd

スコア95

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

実行計画追加

2017/10/10 02:35

投稿

msd
msd

スコア95

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
+ ---------------------------------------------------------------------------------------