APPROX_COUNT_DISTINCT関数とは
APPROX_COUNT_DISTINCT関数とは、重複していない値の近似カウントを集計する関数で、例えば次のような問い合わせにおいて、prod_idでグループ化したうちの重複していないcust_idのカウントを集計する。
SELECT prod_id, APPROX_COUNT_DISTINCT(cust_id) AS "Number of Customers" FROM sales GROUP BY prod_id; ORDER BY prod_id;
これを従来のCOUNT関数を使って書くと
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関数の結果を横に並べたものである。
（前者の結果しかないように見えるが、横スクロールして後者の結果と比べてみてほしい。）
１回目にORDER BYあり、２回目にORDER BYなしの結果をそれぞれ確認した。
なお、各測定前に共有プールとバッファ・キャッシュのフラッシュを行ってから、SQL文の実行を行っている。
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」オペレーションの負荷が甚大になることが予想されるので、両者の違いが顕著になるかもしれない。
正確なカウントは必要なのか？
検索条件を入力して「◯件ヒットしました。」と表示させるアプリケーションは多いが、その値の正確性を１桁単位で求めてもあまり意味のないことが多い。
チューニングの現場ではCOUNT関数の負荷が問題となっていることが多いので、このAPPROX_COUNT_DISTINCT関数は意外と使えるかもしれない。