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

Oracle12c 新機能:APPROX_COUNT_DISTINCT関数

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関数の結果を横に並べたものである。
(前者の結果しかないように見えるが、横スクロールして後者の結果と比べてみてほしい。)

1回目にORDER BYあり、2回目に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」オペレーションの負荷が甚大になることが予想されるので、両者の違いが顕著になるかもしれない。

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

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

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