Approximate Count Distinct–Oracle Database 12c

 

Not every query requires a completely accurate result, for example “How many distinct individuals visited our website last week?”.

New SQL function for approximate results for COUNT DISTINCT aggregates –APPROX_COUNT_DISTINCT()

• Approximate results can be significantly faster and use less resources than exact calculations
• –5x to 50x ++ times faster (depending upon number of distinct values and complexity of SQL)
  –Accuracy > 97% (with 95% confidence)

Example:

SQL> select count(distinct prod_id) from sh.sales;

COUNT(DISTINCTPROD_ID)
----------------------
                    72

SQL> select approx_count_distinct(prod_id) from sh.sales;

APPROX_COUNT_DISTINCT(PROD_ID)
------------------------------
                            72

SQL>  select count(distinct cust_id) from sh.sales;

COUNT(DISTINCTCUST_ID)
----------------------
                  7059

SQL> select approx_count_distinct(cust_id) from sh.sales;

APPROX_COUNT_DISTINCT(CUST_ID)
------------------------------
                          7014