日別アーカイブ: 2016/02/14

Oracle12c 新機能:APPROX_COUNT_DISTINCT関数

APPROX_COUNT_DISTINCT関数とは

APPROX_COUNT_DISTINCT関数とは、重複していない値の近似カウントを集計する関数で、例えば次のような問い合わせにおいて、prod_idでグループ化したうちの重複していないcust_idのカウントを集計する。

1
2
3
4
SELECT prod_id, APPROX_COUNT_DISTINCT(cust_id) AS "Number of Customers"
FROM sales
GROUP BY prod_id;
ORDER BY prod_id;

これを従来のCOUNT関数を使って書くと

1
2
3
4
SELECT prod_id, COUNT(DISTINCT cust_id) AS "Number of Customers"
FROM sales
GROUP BY prod_id;
ORDER BY prod_id;

となる。

マニュアルからの抜粋

Oracle® Database新機能ガイド
12cリリース1 (12.1)
B71327-05

には以下の記述がある。

1.2 重複していない値の近似カウント

最適化された新しいSQL関数APPROX_COUNT_DISTINCT()により、重複していない値の近似カウントが集約されます。大量のデータの処理速度が完全集約よりも大幅に上がりますが、これは特に、重複していない値が多数含まれるデータ・セットの場合に顕著であり、完全集約結果との偏差は無視できる程度です。

現在のデータ分析では、一般的な操作で重複していない値をカウントすることが求められます。処理時間およびリソース消費を桁違いに最適化すると同時にほとんど完全な結果を提供することにより、既存の処理速度を上げ、分析の洞察力を数段高めることができます。

実際に試してみた

マニュアルに書いてある「桁違いの最適化」とは一体どんなものなのか気になったので、サンプルスキーマの「SH.SALES」表(918,843件)を題材に実際に確認してみた。

以下は、COUNT関数とAPPROX_COUNT_DISTINCT関数の結果を横に並べたものである。
(前者の結果しかないように見えるが、横スクロールして後者の結果と比べてみてほしい。)

1回目にORDER BYあり、2回目にORDER BYなしの結果をそれぞれ確認した。

なお、各測定前に共有プールとバッファ・キャッシュのフラッシュを行ってから、SQL文の実行を行っている。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
00001 L|SQL> SELECT prod_id, COUNT(DISTINCT cust_id) AS "Number of Customers"                                         ||00001 R|SQL> SELECT prod_id, APPROX_COUNT_DISTINCT(cust_id) AS "Number of Customers"                                 
00002  |  2  FROM sales                                                                                               ||00002  |  2  FROM sales                                                                                              
00003  |  3  GROUP BY prod_id                                                                                         ||00003  |  3  GROUP BY prod_id                                                                                        
00004  |  4  ORDER BY prod_id;                                                                                        ||00004  |  4  ORDER BY prod_id;                                                                                       
00005  |                                                                                                              ||00005  |                                                                                                             
00006  |   PROD_ID Number of Customers                                                                                ||00006  |   PROD_ID Number of Customers                                                                               
00007  |---------- -------------------                                                                                ||00007  |---------- -------------------                                                                               
00008 L|        13                2492                                                                                ||00008 R|        13                2516                                                                               
00009 L|        14                2039                                                                                ||00009 R|        14                2030                                                                               
00010 L|        15                2122                                                                                ||00010 R|        15                2105                                                                               
00011 L|        16                2384                                                                                ||00011 R|        16                2367                                                                               
00012 L|        17                2100                                                                                ||00012 R|        17                2093                                                                               
00013 L|        18                3028                                                                                ||00013 R|        18                2975                                                                               
00014 L|        19                2617                                                                                ||00014 R|        19                2630                                                                               
00015 L|        20                3795                                                                                ||00015 R|        20                3791                                                                               
00016 L|        21                2334                                                                                ||00016 R|        21                2365                                                                               
00017 L|        22                1416                                                                                ||00017 R|        22                1408                                                                               
00018 L|        23                5183                                                                                ||00018 R|        23                5104                                                                               
00019  |                                                                                                              ||00019  |                                                                                                             
00020  |   PROD_ID Number of Customers                                                                                ||00020  |   PROD_ID Number of Customers                                                                               
00021  |---------- -------------------                                                                                ||00021  |---------- -------------------                                                                               
00022 L|        24                4817                                                                                ||00022 R|        24                4754                                                                               
00023 L|        25                5068                                                                                ||00023 R|        25                4991                                                                               
00024 L|        26                4948                                                                                ||00024 R|        26                4894                                                                               
00025 L|        27                3801                                                                                ||00025 R|        27                3726                                                                               
00026 L|        28                4572                                                                                ||00026 R|        28                4537                                                                               
00027 L|        29                2295                                                                                ||00027 R|        29                2305                                                                               
00028 L|        30                6154                                                                                ||00028 R|        30                6134                                                                               
00029 L|        31                5586                                                                                ||00029 R|        31                5463                                                                               
00030 L|        32                4100                                                                                ||00030 R|        32                4025                                                                               
00031 L|        33                5389                                                                                ||00031 R|        33                5278                                                                               
00032 L|        34                4192                                                                                ||00032 R|        34                4142                                                                               
00033  |                                                                                                              ||00033  |                                                                                                             
00034  |   PROD_ID Number of Customers                                                                                ||00034  |   PROD_ID Number of Customers                                                                               
00035  |---------- -------------------                                                                                ||00035  |---------- -------------------                                                                               
00036 L|        35                4965                                                                                ||00036 R|        35                4950                                                                               
00037 L|        36                3302                                                                                ||00037 R|        36                3327                                                                               
00038 L|        37                4802                                                                                ||00038 R|        37                4735                                                                               
00039 L|        38                3395                                                                                ||00039 R|        38                3452                                                                               
00040 L|        39                3433                                                                                ||00040 R|        39                3404                                                                               
00041 L|        40                5972                                                                                ||00041 R|        40                5858                                                                               
00042 L|        41                3738                                                                                ||00042 R|        41                3759                                                                               
00043 L|        42                3951                                                                                ||00043 R|        42                3938                                                                               
00044 L|        43                3016                                                                                ||00044 R|        43                3056                                                                               
00045 L|        44                2571                                                                                ||00045 R|        44                2587                                                                               
00046 L|        45                3549                                                                                ||00046 R|        45                3499                                                                               
00047  |                                                                                                              ||00047  |                                                                                                             
00048  |   PROD_ID Number of Customers                                                                                ||00048  |   PROD_ID Number of Customers                                                                               
00049  |---------- -------------------                                                                                ||00049  |---------- -------------------                                                                               
00050 L|        46                4107                                                                                ||00050 R|        46                4054                                                                               
00051 L|        47                3198                                                                                ||00051 R|        47                3176                                                                               
00052 L|        48                6010                                                                                ||00052 R|        48                5883                                                                               
00053 L|       113                4640                                                                                ||00053 R|       113                4598                                                                               
00054 L|       114                4230                                                                                ||00054 R|       114                4183                                                                               
00055 L|       115                3847                                                                                ||00055 R|       115                3832                                                                               
00056 L|       116                4929                                                                                ||00056 R|       116                4924                                                                               
00057 L|       117                4672                                                                                ||00057 R|       117                4632                                                                               
00058 L|       118                4214                                                                                ||00058 R|       118                4200                                                                               
00059 L|       119                4898                                                                                ||00059 R|       119                4858                                                                               
00060 L|       120                5224                                                                                ||00060 R|       120                5135                                                                               
00061  |                                                                                                              ||00061  |                                                                                                             
00062  |   PROD_ID Number of Customers                                                                                ||00062  |   PROD_ID Number of Customers                                                                               
00063  |---------- -------------------                                                                                ||00063  |---------- -------------------                                                                               
00064 L|       121                3517                                                                                ||00064 R|       121                3499                                                                               
00065 L|       122                1572                                                                                ||00065 R|       122                1559                                                                               
00066 L|       123                4491                                                                                ||00066 R|       123                4441                                                                               
00067 L|       124                4309                                                                                ||00067 R|       124                4310                                                                               
00068 L|       125                4679                                                                                ||00068 R|       125                4650                                                                               
00069 L|       126                4253                                                                                ||00069 R|       126                4202                                                                               
00070 L|       127                4757                                                                                ||00070 R|       127                4710                                                                               
00071 L|       128                5196                                                                                ||00071 R|       128                5135                                                                               
00072 L|       129                2425                                                                                ||00072 R|       129                2419                                                                               
00073 L|       130                5428                                                                                ||00073 R|       130                5336                                                                               
00074 L|       131                5013                                                                                ||00074 R|       131                4907                                                                               
00075  |                                                                                                              ||00075  |                                                                                                             
00076  |   PROD_ID Number of Customers                                                                                ||00076  |   PROD_ID Number of Customers                                                                               
00077  |---------- -------------------                                                                                ||00077  |---------- -------------------                                                                               
00078 L|       132                4770                                                                                ||00078 R|       132                4688                                                                               
00079 L|       133                5201                                                                                ||00079 R|       133                5114                                                                               
00080 L|       134                3629                                                                                ||00080 R|       134                3594                                                                               
00081 L|       135                3996                                                                                ||00081 R|       135                3943                                                                               
00082 L|       136                 397                                                                                ||00082 R|       136                 395                                                                               
00083 L|       137                3993                                                                                ||00083 R|       137                3977                                                                               
00084 L|       138                2763                                                                                ||00084 R|       138                2716                                                                               
00085 L|       139                4217                                                                                ||00085 R|       139                4106                                                                               
00086 L|       140                5097                                                                                ||00086 R|       140                5032                                                                               
00087 L|       141                3546                                                                                ||00087 R|       141                3499                                                                               
00088 L|       142                3136                                                                                ||00088 R|       142                3071                                                                               
00089  |                                                                                                              ||00089  |                                                                                                             
00090  |   PROD_ID Number of Customers                                                                                ||00090  |   PROD_ID Number of Customers                                                                               
00091  |---------- -------------------                                                                                ||00091  |---------- -------------------                                                                               
00092 L|       143                2375                                                                                ||00092 R|       143                2395                                                                               
00093 L|       144                2289                                                                                ||00093 R|       144                2254                                                                               
00094 L|       145                2814                                                                                ||00094 R|       145                2807                                                                               
00095 L|       146                4796                                                                                ||00095 R|       146                4716                                                                               
00096 L|       147                3050                                                                                ||00096 R|       147                3073                                                                               
00097 L|       148                5150                                                                                ||00097 R|       148                5049                                                                               
00098  |                                                                                                              ||00098  |                                                                                                             
00099  |72行が選択されました。                                                                                        ||00099  |72行が選択されました。                                                                                       
00100  |                                                                                                              ||00100  |                                                                                                             
00101 L|経過: 00:00:01.27                                                                                             ||00101 R|経過: 00:00:00.92                                                                                            
00102  |                                                                                                              ||00102  |                                                                                                             
00103  |実行計画                                                                                                      ||00103  |実行計画                                                                                                     
00104  |----------------------------------------------------------                                                    ||00104  |----------------------------------------------------------                                                   
00105 L|Plan hash value: 932069919                                                                                    ||00105 R|Plan hash value: 4109827725                                                                                  
00106  |                                                                                                              ||00106  |                                                                                                             
00107 L|------------------------------------------------------------------------------------------------------------  ||00107 R|----------------------------------------------------------------------------------------------               
00108 L|| Id  | Operation              | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |  ||00108 R|| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |               
00109 L|------------------------------------------------------------------------------------------------------------  ||00109 R|----------------------------------------------------------------------------------------------               
00110 L||   0 | SELECT STATEMENT       |           |    72 |  1224 |       |  2638   (2)| 00:00:01 |       |       |  ||00110 R||   0 | SELECT STATEMENT     |       |    72 |   648 |   537   (6)| 00:00:01 |       |       |               
00111 L||   1 |  SORT GROUP BY         |           |    72 |  1224 |       |  2638   (2)| 00:00:01 |       |       |  ||00111 R||   1 |  SORT GROUP BY APPROX|       |    72 |   648 |   537   (6)| 00:00:01 |       |       |               
00112 L||   2 |   VIEW                 | VM_NWVW_1 |   359K|  5966K|       |  2638   (2)| 00:00:01 |       |       |  ||00112 R||   2 |   PARTITION RANGE ALL|       |   918K|  8075K|   514   (1)| 00:00:01 |     1 |    28 |               
00113 L||   3 |    HASH GROUP BY       |           |   359K|  3158K|    17M|  2638   (2)| 00:00:01 |       |       |  ||00113 R||   3 |    TABLE ACCESS FULL | SALES |   918K|  8075K|   514   (1)| 00:00:01 |     1 |    28 |               
00114 L||   4 |     PARTITION RANGE ALL|           |   918K|  8075K|       |   514   (1)| 00:00:01 |     1 |    28 |  ||00114 R|----------------------------------------------------------------------------------------------               
00115 L||   5 |      TABLE ACCESS FULL | SALES     |   918K|  8075K|       |   514   (1)| 00:00:01 |     1 |    28 |  ||       |                                                                                                             
00116 L|------------------------------------------------------------------------------------------------------------  ||       |                                                                                                             
00117  |                                                                                                              ||00115  |                                                                                                             
00118  |                                                                                                              ||00116  |                                                                                                             
00119  |統計                                                                                                          ||00117  |統計                                                                                                         
00120  |----------------------------------------------------------                                                    ||00118  |----------------------------------------------------------                                                   
00121 L|       1936  recursive calls                                                                                  ||00119 R|       1956  recursive calls                                                                                 
00122  |          0  db block gets                                                                                    ||00120  |          0  db block gets                                                                                   
00123 L|       4914  consistent gets                                                                                  ||00121 R|       4921  consistent gets                                                                                 
00124 L|       1772  physical reads                                                                                   ||00122 R|       1729  physical reads                                                                                  
00125  |          0  redo size                                                                                        ||00123  |          0  redo size                                                                                       
00126 L|       2133  bytes sent via SQL*Net to client                                                                 ||00124 R|       2134  bytes sent via SQL*Net to client                                                                
00127  |        595  bytes received via SQL*Net from client                                                           ||00125  |        595  bytes received via SQL*Net from client                                                          
00128  |          6  SQL*Net roundtrips to/from client                                                                ||00126  |          6  SQL*Net roundtrips to/from client                                                               
00129  |        136  sorts (memory)                                                                                   ||00127  |        136  sorts (memory)                                                                                  
00130  |          0  sorts (disk)                                                                                     ||00128  |          0  sorts (disk)                                                                                    
00131  |         72  rows processed                                                                                   ||00129  |         72  rows processed                                                                                  
00132  |                                                                                                              ||00130  |                                                                                                             
00133  |                                                                                                              ||00131  |                                                                                                             
00134 L|SQL> SELECT prod_id, COUNT(DISTINCT cust_id) AS "Number of Customers"                                         ||00132 R|SQL> SELECT prod_id, APPROX_COUNT_DISTINCT(cust_id) AS "Number of Customers"                                 
00135  |  2  FROM sales                                                                                               ||00133  |  2  FROM sales                                                                                              
00136  |  3  GROUP BY prod_id;                                                                                        ||00134  |  3  GROUP BY prod_id;                                                                                       
00137  |                                                                                                              ||00135  |                                                                                                             
00138  |   PROD_ID Number of Customers                                                                                ||00136  |   PROD_ID Number of Customers                                                                               
00139  |---------- -------------------                                                                                ||00137  |---------- -------------------                                                                               
00140 L|        22                1416                                                                                ||00138 R|        22                1408                                                                               
00141 L|        25                5068                                                                                ||00139 R|        25                4991                                                                               
00142 L|        30                6154                                                                                ||00140 R|        30                6134                                                                               
00143 L|        34                4192                                                                                ||00141 R|        34                4142                                                                               
00144 L|        42                3951                                                                                ||00142 R|        42                3938                                                                               
00145 L|        43                3016                                                                                ||00143 R|        43                3056                                                                               
00146 L|       123                4491                                                                                ||00144 R|       123                4441                                                                               
00147 L|       129                2425                                                                                ||00145 R|       129                2419                                                                               
00148 L|       138                2763                                                                                ||00146 R|       138                2716                                                                               
00149 L|        13                2492                                                                                ||00147 R|        13                2516                                                                               
00150 L|        28                4572                                                                                ||00148 R|        28                4537                                                                               
00151  |                                                                                                              ||00149  |                                                                                                             
00152  |   PROD_ID Number of Customers                                                                                ||00150  |   PROD_ID Number of Customers                                                                               
00153  |---------- -------------------                                                                                ||00151  |---------- -------------------                                                                               
00154 L|        29                2295                                                                                ||00152 R|        29                2305                                                                               
00155 L|        44                2571                                                                                ||00153 R|        44                2587                                                                               
00156 L|        47                3198                                                                                ||00154 R|        47                3176                                                                               
00157 L|       113                4640                                                                                ||00155 R|       113                4598                                                                               
00158 L|       116                4929                                                                                ||00156 R|       116                4924                                                                               
00159 L|       120                5224                                                                                ||00157 R|       120                5135                                                                               
00160 L|       128                5196                                                                                ||00158 R|       128                5135                                                                               
00161 L|       147                3050                                                                                ||00159 R|       147                3073                                                                               
00162 L|       121                3517                                                                                ||00160 R|       121                3499                                                                               
00163 L|       134                3629                                                                                ||00161 R|       134                3594                                                                               
00164 L|       144                2289                                                                                ||00162 R|       144                2254                                                                               
00165  |                                                                                                              ||00163  |                                                                                                             
00166  |   PROD_ID Number of Customers                                                                                ||00164  |   PROD_ID Number of Customers                                                                               
00167  |---------- -------------------                                                                                ||00165  |---------- -------------------                                                                               
00168 L|        14                2039                                                                                ||00166 R|        14                2030                                                                               
00169 L|        20                3795                                                                                ||00167 R|        20                3791                                                                               
00170 L|        21                2334                                                                                ||00168 R|        21                2365                                                                               
00171 L|        26                4948                                                                                ||00169 R|        26                4894                                                                               
00172 L|        31                5586                                                                                ||00170 R|        31                5463                                                                               
00173 L|       132                4770                                                                                ||00171 R|       132                4688                                                                               
00174 L|       148                5150                                                                                ||00172 R|       148                5049                                                                               
00175 L|       141                3546                                                                                ||00173 R|       141                3499                                                                               
00176 L|        24                4817                                                                                ||00174 R|        24                4754                                                                               
00177 L|        32                4100                                                                                ||00175 R|        32                4025                                                                               
00178 L|        46                4107                                                                                ||00176 R|        46                4054                                                                               
00179  |                                                                                                              ||00177  |                                                                                                             
00180  |   PROD_ID Number of Customers                                                                                ||00178  |   PROD_ID Number of Customers                                                                               
00181  |---------- -------------------                                                                                ||00179  |---------- -------------------                                                                               
00182 L|       117                4672                                                                                ||00180 R|       117                4632                                                                               
00183 L|       119                4898                                                                                ||00181 R|       119                4858                                                                               
00184 L|       133                5201                                                                                ||00182 R|       133                5114                                                                               
00185 L|       137                3993                                                                                ||00183 R|       137                3977                                                                               
00186 L|       143                2375                                                                                ||00184 R|       143                2395                                                                               
00187 L|        17                2100                                                                                ||00185 R|        17                2093                                                                               
00188 L|        23                5183                                                                                ||00186 R|        23                5104                                                                               
00189 L|        35                4965                                                                                ||00187 R|        35                4950                                                                               
00190 L|        37                4802                                                                                ||00188 R|        37                4735                                                                               
00191 L|        38                3395                                                                                ||00189 R|        38                3452                                                                               
00192 L|        48                6010                                                                                ||00190 R|        48                5883                                                                               
00193  |                                                                                                              ||00191  |                                                                                                             
00194  |   PROD_ID Number of Customers                                                                                ||00192  |   PROD_ID Number of Customers                                                                               
00195  |---------- -------------------                                                                                ||00193  |---------- -------------------                                                                               
00196 L|       125                4679                                                                                ||00194 R|       125                4650                                                                               
00197 L|       135                3996                                                                                ||00195 R|       135                3943                                                                               
00198 L|        33                5389                                                                                ||00196 R|        33                5278                                                                               
00199 L|        40                5972                                                                                ||00197 R|        40                5858                                                                               
00200 L|        41                3738                                                                                ||00198 R|        41                3759                                                                               
00201 L|        45                3549                                                                                ||00199 R|        45                3499                                                                               
00202 L|       114                4230                                                                                ||00200 R|       114                4183                                                                               
00203 L|       130                5428                                                                                ||00201 R|       130                5336                                                                               
00204 L|       131                5013                                                                                ||00202 R|       131                4907                                                                               
00205 L|       136                 397                                                                                ||00203 R|       136                 395                                                                               
00206 L|       140                5097                                                                                ||00204 R|       140                5032                                                                               
00207  |                                                                                                              ||00205  |                                                                                                             
00208  |   PROD_ID Number of Customers                                                                                ||00206  |   PROD_ID Number of Customers                                                                               
00209  |---------- -------------------                                                                                ||00207  |---------- -------------------                                                                               
00210 L|        18                3028                                                                                ||00208 R|        18                2975                                                                               
00211 L|        27                3801                                                                                ||00209 R|        27                3726                                                                               
00212 L|        36                3302                                                                                ||00210 R|        36                3327                                                                               
00213 L|       115                3847                                                                                ||00211 R|       115                3832                                                                               
00214 L|       146                4796                                                                                ||00212 R|       146                4716                                                                               
00215 L|       124                4309                                                                                ||00213 R|       124                4310                                                                               
00216 L|       142                3136                                                                                ||00214 R|       142                3071                                                                               
00217 L|       145                2814                                                                                ||00215 R|       145                2807                                                                               
00218 L|        15                2122                                                                                ||00216 R|        15                2105                                                                               
00219 L|        19                2617                                                                                ||00217 R|        19                2630                                                                               
00220 L|        39                3433                                                                                ||00218 R|        39                3404                                                                               
00221  |                                                                                                              ||00219  |                                                                                                             
00222  |   PROD_ID Number of Customers                                                                                ||00220  |   PROD_ID Number of Customers                                                                               
00223  |---------- -------------------                                                                                ||00221  |---------- -------------------                                                                               
00224 L|       118                4214                                                                                ||00222 R|       118                4200                                                                               
00225 L|       126                4253                                                                                ||00223 R|       126                4202                                                                               
00226 L|       127                4757                                                                                ||00224 R|       127                4710                                                                               
00227 L|        16                2384                                                                                ||00225 R|        16                2367                                                                               
00228 L|       122                1572                                                                                ||00226 R|       122                1559                                                                               
00229 L|       139                4217                                                                                ||00227 R|       139                4106                                                                               
00230  |                                                                                                              ||00228  |                                                                                                             
00231  |72行が選択されました。                                                                                        ||00229  |72行が選択されました。                                                                                       
00232  |                                                                                                              ||00230  |                                                                                                             
00233 L|経過: 00:00:00.90                                                                                             ||00231 R|経過: 00:00:00.73                                                                                            
00234  |                                                                                                              ||00232  |                                                                                                             
00235  |実行計画                                                                                                      ||00233  |実行計画                                                                                                     
00236  |----------------------------------------------------------                                                    ||00234  |----------------------------------------------------------                                                   
00237 L|Plan hash value: 2475333094                                                                                   ||00235 R|Plan hash value: 3604305554                                                                                  
00238  |                                                                                                              ||00236  |                                                                                                             
00239 L|------------------------------------------------------------------------------------------------------------  ||00237 R|----------------------------------------------------------------------------------------------               
00240 L|| Id  | Operation              | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |  ||00238 R|| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |               
00241 L|------------------------------------------------------------------------------------------------------------  ||00239 R|----------------------------------------------------------------------------------------------               
00242 L||   0 | SELECT STATEMENT       |           |    72 |  1224 |       |  2638   (2)| 00:00:01 |       |       |  ||00240 R||   0 | SELECT STATEMENT     |       |    72 |   648 |   537   (6)| 00:00:01 |       |       |               
00243 L||   1 |  HASH GROUP BY         |           |    72 |  1224 |       |  2638   (2)| 00:00:01 |       |       |  ||00241 R||   1 |  HASH GROUP BY APPROX|       |    72 |   648 |   537   (6)| 00:00:01 |       |       |               
00244 L||   2 |   VIEW                 | VM_NWVW_1 |   359K|  5966K|       |  2638   (2)| 00:00:01 |       |       |  ||00242 R||   2 |   PARTITION RANGE ALL|       |   918K|  8075K|   514   (1)| 00:00:01 |     1 |    28 |               
00245 L||   3 |    HASH GROUP BY       |           |   359K|  3158K|    17M|  2638   (2)| 00:00:01 |       |       |  ||00243 R||   3 |    TABLE ACCESS FULL | SALES |   918K|  8075K|   514   (1)| 00:00:01 |     1 |    28 |               
00246 L||   4 |     PARTITION RANGE ALL|           |   918K|  8075K|       |   514   (1)| 00:00:01 |     1 |    28 |  ||00244 R|----------------------------------------------------------------------------------------------               
00247 L||   5 |      TABLE ACCESS FULL | SALES     |   918K|  8075K|       |   514   (1)| 00:00:01 |     1 |    28 |  ||       |                                                                                                             
00248 L|------------------------------------------------------------------------------------------------------------  ||       |                                                                                                             
00249  |                                                                                                              ||00245  |                                                                                                             
00250  |                                                                                                              ||00246  |                                                                                                             
00251  |統計                                                                                                          ||00247  |統計                                                                                                         
00252  |----------------------------------------------------------                                                    ||00248  |----------------------------------------------------------                                                   
00253 L|       2432  recursive calls                                                                                  ||00249 R|       2313  recursive calls                                                                                 
00254  |          0  db block gets                                                                                    ||00250  |          0  db block gets                                                                                   
00255 L|       5669  consistent gets                                                                                  ||00251 R|       5458  consistent gets                                                                                 
00256 L|       1794  physical reads                                                                                   ||00252 R|       1765  physical reads                                                                                  
00257  |          0  redo size                                                                                        ||00253  |          0  redo size                                                                                       
00258 L|       2133  bytes sent via SQL*Net to client                                                                 ||00254 R|       2134  bytes sent via SQL*Net to client                                                                
00259  |        595  bytes received via SQL*Net from client                                                           ||00255  |        595  bytes received via SQL*Net from client                                                          
00260  |          6  SQL*Net roundtrips to/from client                                                                ||00256  |          6  SQL*Net roundtrips to/from client                                                               
00261 L|        239  sorts (memory)                                                                                   ||00257 R|        229  sorts (memory)                                                                                  
00262  |          0  sorts (disk)                                                                                     ||00258  |          0  sorts (disk)                                                                                    
00263  |         72  rows processed                                                                                   ||00259  |         72  rows processed                                                                                  
00264 L|                                                                                                              ||       |                                                                                                             

何が違うのか?

行番号の後に「L」あるいは「R」がついている行は、左右の結果が異なるものを示している。

近似値というからには両者はほとんど同じ結果でたまに違う値となることを予想していたのだが、あにはからんや(72行という結果は同じだが)すべての行で異なるカウント値となった。

また、統計情報はほとんど同じだが、実行計画が大きく異なっている。

COUNT関数の場合「PARTITION RANGE ALL」で全行を取得した後、「HASH GROUP BY」で約35万9千行のビユーを作成(その際17MBのHASH AREAを使用)し、さらに「SORT GROUP BY」で72行に絞り込んでいる。

一方、APPROX_COUNT_DISTINCT関数の場合全行を取得するまでは同じだが、「SORT GROUP BY APPROX」という新たなオペレーションによって一気に72行まで絞り込んでいる。その際コストの上積み分もほとんどない。(514→538)

今回の場合、テーブルサイズがそれほど大きくないので、実行時間で大きな違いはなかったが、巨大なテーブルの場合は「HASH GROUP BY」オペレーションの負荷が甚大になることが予想されるので、両者の違いが顕著になるかもしれない。

正確なカウントは必要なのか?

検索条件を入力して「◯件ヒットしました。」と表示させるアプリケーションは多いが、その値の正確性を1桁単位で求めてもあまり意味のないことが多い。

チューニングの現場ではCOUNT関数の負荷が問題となっていることが多いので、このAPPROX_COUNT_DISTINCT関数は意外と使えるかもしれない。