Oracle BITMAP_ Functions for Exact Distinct Counts

Approximate sketches make distinct counts fast and mergeable, but approximate. When the count must be exact, for example customers per month that must add up across any period, Oracle's BITMAP_ functions do it with bitmaps: one bit per distinct integer, stored per group and merged with a bitwise OR.

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:

bitmap_bucket_number(expr)        -- which bitmap a value belongs to
bitmap_bit_position(expr)         -- its bit within that bitmap
bitmap_construct_agg(position)    -- builds a bitmap from bit positions
bitmap_or_agg(bitmap)             -- merges bitmaps
bitmap_count(bitmap)              -- counts the bits that are set

Each value is split into a bucket and a bit position within the bucket's bitmap. The expression must be an integer, such as a customer ID.

Distinct Customers per Month

Example:

-- distinct customers per month, counted with bitmaps
select month, sum(bitmap_count(bm)) as customers
from   (select trunc(booked_at, 'MM') as month,
               bitmap_bucket_number(customer_id) as bucket,
               bitmap_construct_agg(bitmap_bit_position(customer_id)) as bm
        from   bookings
        group  by trunc(booked_at, 'MM'), bitmap_bucket_number(customer_id))
group  by month
order  by month;

Output:

MONTH             CUSTOMERS
______________ ____________
01-OCT-2025               7
01-NOV-2025              60
01-DEC-2025              84
01-JAN-2026             102
01-FEB-2026              86
01-MAR-2026              52

6 rows selected.

The inner query builds one bitmap per month and bucket; the outer one counts the bits and adds up the buckets. The counts match COUNT(DISTINCT) exactly; note November, 60, where the approximate sketch estimated 59.

Merge Months Exactly

BITMAP_OR_AGG merges the monthly bitmaps of each bucket, so a customer who booked in several months is counted once.

Example:

-- merge the monthly bitmaps into an exact count for the whole period
select sum(bitmap_count(bm)) as all_customers
from   (select bucket, bitmap_or_agg(bm) as bm
        from   (select trunc(booked_at, 'MM') as month,
                       bitmap_bucket_number(customer_id) as bucket,
                       bitmap_construct_agg(bitmap_bit_position(customer_id)) as bm
                from   bookings
                group  by trunc(booked_at, 'MM'), bitmap_bucket_number(customer_id))
        group  by bucket);

Output:

   ALL_CUSTOMERS
________________
             120

The merged count is 120, the exact number of distinct customers, where merged approximate sketches gave 119.

Things to Know

• Store the per-group bitmaps with their bucket numbers in a table or materialized view, then merge any combination of groups on demand.
• The functions work only on integers; map other keys to integers first.
• Bitmaps are larger than approximate sketches, the price of exactness.

Related Guides

• Oracle APPROX_COUNT_DISTINCT Function
• Oracle COUNT Function

Conclusion

The BITMAP_ functions count distinct integers exactly with bitmaps that can be stored per group and merged with BITMAP_OR_AGG. Use them instead of approximate sketches when distinct counts over combined periods must be exact.