APPROX_COUNT_DISTINCT

APPROX_COUNT_DISTINCT is a new function available starting with Oracle Database 12c Release (12.1.0.2). It is easy to guess when the name of the function is properly selected. This function returns the APPROXIMATE number of rows that contain distinct value of expression. Be advise, it provides APPROXIMATE or nearly accurate count. The function is used instead of COUNT(distinct expr) which returns the exact count. APPROX_COUNT_DISTINCT(expr) processes large amount of data much faster than count with a very negligible deviation from the exact result. It ignores rows that contain NULL value for expr and returns a NUMBER.

Queries that use the APPROX_COUNT_DISTINCT Function execute much faster, with a low relative error of around 2%. Approximation is warranted for queries that return a large number of distinct values, in the millions or more per query, or per group, if there is a group by clause.
For smaller sets of distinct values, in the thousands, approximation might be slower than a precise count.

SELECT APPROX_COUNT_DISTINCT(manager_id) AS "Active Managers"
  FROM employees;

Active Managers
---------------
             18

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

   PROD_ID Number of Customers
---------- -------------------
        13                2516
        14                2030
        15                2105
        16                2367
        17                2093
        18                2975
        19                2630
        20                3791

Try running this on your 11g Oracle server. If you didn't know already, Oracle 11g had this Function built in but was never documented. Starting With 12c, it is fully supported and documented both.

If you’ve got a question for me, you can email me at pbaniya04@gmail.com. If you liked this article and want to read more tips, articles, and tutorial related database, please subscribe to my blog and share this with your buddies.