COUNT(DISTINCT) has to remember every distinct value it has seen, which on billions of rows takes time and memory. Dashboards and trend reports rarely need the exact number of distinct visitors or customers; within a percent or two is fine. APPROX_COUNT_DISTINCT estimates the number of distinct values with a small sketch, many times faster, and its detail variants let you store sketches and combine them later.
Code for This Guide
The main examples are in the examples/aggregate-functions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
approx_count_distinct(expr) approx_count_distinct_detail(expr) -- a sketch per group, as a BLOB approx_count_distinct_agg(detail) -- merges sketches to_approx_count_distinct(detail) -- turns a sketch into a number
Estimate Distinct Values
The first query of this example compares the exact and approximate number of customers who booked, and estimates the distinct booking references.
Example:
select count(distinct customer_id) as exact_customers,
approx_count_distinct(customer_id) as approx_customers,
approx_count_distinct(booking_ref) as approx_refs
from bookings;
-- the three routes with the most flights, found approximately
select route_id, approx_count(*) as flights
from flights
group by route_id
having approx_rank(order by approx_count(*) desc) <= 3;
-- the cabin that earned the most
select cabin, approx_sum(fare) as revenue
from tickets
group by cabin
having approx_rank(order by approx_sum(fare) desc) <= 1;Output:
EXACT_CUSTOMERS APPROX_CUSTOMERS APPROX_REFS
__________________ ___________________ ______________
120 119 691
ROUTE_ID FLIGHTS
___________ __________
27 90
24 90
23 90
CABIN REVENUE
___________ ____________
BUSINESS 618345.79The estimate is 119 customers against an exact 120, and 691 booking references against 700. On a table this small the speed difference is invisible; on very large tables it is large. The other two queries use APPROX_COUNT and APPROX_SUM, covered separately.
Store and Merge Sketches
Distinct counts cannot be added: a customer who booked in two months would be counted twice. The detail functions keep the sketch instead of the number, so monthly results can be stored and merged into a correct total later.
Example:
-- keep a mergeable summary per month, then combine the months without the raw rows
create table monthly_customers as
select trunc(booked_at, 'MM') as month,
approx_count_distinct_detail(customer_id) as detail
from bookings
group by trunc(booked_at, 'MM');
select month, to_approx_count_distinct(detail) as customers
from monthly_customers
order by month;
select to_approx_count_distinct(approx_count_distinct_agg(detail)) as all_months
from monthly_customers;Output:
Table MONTHLY_CUSTOMERS created.
MONTH CUSTOMERS
______________ ____________
01-OCT-2025 7
01-NOV-2025 59
01-DEC-2025 83
01-JAN-2026 102
01-FEB-2026 86
01-MAR-2026 52
6 rows selected.
ALL_MONTHS
_____________
119The monthly counts add up to far more than 119, but merging the stored sketches with APPROX_COUNT_DISTINCT_AGG gives 119 for the whole period. Drop the work table afterward with drop table monthly_customers purge.
Things to Know
- Setting the parameter APPROX_FOR_COUNT_DISTINCT to TRUE makes Oracle use the approximate algorithm for COUNT(DISTINCT) automatically, without changing queries.
- Use the exact COUNT(DISTINCT) for billing, auditing, and anything where every unit counts.
- Sketches in a materialized view make distinct counts over any combination of periods fast.
Related Guides
Conclusion
APPROX_COUNT_DISTINCT estimates distinct counts within a small error much faster than COUNT(DISTINCT). Its _DETAIL, _AGG, and TO_APPROX_ companions store sketches per group and merge them into correct totals for larger groups.
