This is the single most specialized function in this entire course, worth being upfront about: BITMAP_CONSTRUCT_AGG is Oracle-only, and even on Oracle it's almost never written directly in everyday application SQL. It's included because the curriculum this course is built from explicitly asked for every database-specific aggregate, not just the common ones, and this is a real one.
Learning Objectives
- Explain what BITMAP_CONSTRUCT_AGG actually does, aggregating bit positions into a bitmap.
- Understand the real problem it solves: letting Oracle rewrite an expensive COUNT(DISTINCT ...) into a cheap read of a materialized view.
- Recognize that this is genuinely rare, Oracle-only, internals-level SQL, not a function to reach for casually.
BITMAP_BIT_POSITION Values
Per row, from a helper function
BITMAP_CONSTRUCT_AGG()
Aggregates them into one bitmap
Stored in a Materialized View
The optimizer can rewrite COUNT(DISTINCT) against it
What It Actually Does
BITMAP_CONSTRUCT_AGG takes a group of bit positions and returns the bitmap representing all of them set together, a specialized, Oracle-internal data structure, not a plain integer or string. In practice, it's used paired with two other Oracle functions, BITMAP_BUCKET_NUMBER and BITMAP_BIT_POSITION, which translate a column's distinct values into bucket numbers and bit positions in the first place.
The Real Use Case: Materialized View Query Rewrite
Counting distinct values over a genuinely huge table is expensive, the previous module's lesson on APPROX_COUNT_DISTINCT covered one way to speed it up by trading accuracy. Oracle's bitmap approach is a different trade: build a materialized view that stores a bitmap representation of a column's distinct values, with query rewrite enabled, then let the optimizer silently redirect an ordinary, exact COUNT(DISTINCT ...) against the base table to read that much smaller bitmap instead, with no loss of accuracy, since it's exact, not approximate.
A bitmap built this way has a real size limit tied to Oracle's maximum BLOB size, exceeding it raises error ORA-62577, worth knowing if this is ever actually encountered in Oracle's own error output.
Try It Yourself
This one doesn't get a typical hands-on exercise, the realistic "try it yourself" here is simply recognizing the pattern: seeing BITMAP_CONSTRUCT_AGG inside a materialized view's definition and understanding it's there to support query-rewrite-based distinct counting, not a function to write from scratch in ordinary application code.
Frequently Asked Questions
Would a typical application developer ever write BITMAP_CONSTRUCT_AGG directly?
Rarely, it's almost always generated as part of a specific materialized-view-based optimization strategy, set up once by someone working on Oracle performance internals, not something reached for in day-to-day query writing.
Is this at all related to a regular CREATE BITMAP INDEX?
No, a bitmap index is a separate, more commonly used Oracle feature for indexing low-cardinality columns. BITMAP_CONSTRUCT_AGG is specifically about building a bitmap value inside a materialized view's stored data, for query-rewrite purposes, a narrower, different use case.
Common Beginner Mistakes
Assuming this function exists, or has any equivalent, on any other database in this course
It doesn't, this is about as Oracle-specific as a function gets. None of MySQL, MariaDB, PostgreSQL, SQL Server, or SQLite have anything resembling it.
Reaching for BITMAP_CONSTRUCT_AGG as a general-purpose distinct-counting trick
It's not meant for that, and genuinely rare even within Oracle-specific code, the previous lesson's APPROX_COUNT_DISTINCT (or a simple, properly indexed COUNT(DISTINCT ...)) is the right tool for an ordinary distinct-count performance problem.
Best Practices
- Leave this one to Oracle performance specialists setting up materialized-view-based query rewrite deliberately, not a function to introduce casually.
- Reach for APPROX_COUNT_DISTINCT or a properly indexed exact COUNT(DISTINCT ...) for an ordinary distinct-count performance problem instead.
- If this function does show up in an existing codebase, treat it as a sign a materialized-view-based optimization is already in place, and investigate that view's definition before changing anything nearby.
Interview Questions
What problem does BITMAP_CONSTRUCT_AGG actually solve, and why is it paired with a materialized view specifically?
It lets Oracle store a compact bitmap representation of a column's distinct values inside a materialized view. With query rewrite enabled, Oracle's optimizer can then silently redirect an ordinary COUNT(DISTINCT ...) query against the base table to read that much smaller bitmap instead, getting the exact same exact answer far more cheaply than scanning the full base table.
Why is this function a reasonable thing to mention in an interview, but not something to expect to write casually?
It demonstrates real familiarity with how deep Oracle's optimizer-level features go, beyond ordinary application SQL, but it's genuinely rare, narrow, internals-level functionality that almost nobody hand-writes day to day, understanding what problem it solves matters far more than memorizing its exact syntax.
Summary
BITMAP_CONSTRUCT_AGG aggregates bit positions into a bitmap, used almost exclusively inside an Oracle materialized view to let query rewrite answer a COUNT(DISTINCT ...) exactly, but cheaply. It's genuinely Oracle-only and genuinely rare even there, included here for the course's completeness rather than as something to reach for casually.
That completes Module 15, Advanced Aggregate Functions, and with it, the full SQL course: string and structured aggregation (STRING_AGG/ARRAY_AGG/JSON_ARRAYAGG/JSON_OBJECTAGG), statistical aggregates (variance, standard deviation, median, mode, percentiles), and the handful of narrow, database-specific aggregates (bitwise, boolean, approximate-distinct, checksum, and bitmap) that round out what a real production database actually ships with beyond COUNT, SUM, and AVG.
What's Next?
This is the last lesson in the current SQL course. Future modules, when added, build on this same Beacon Robotics schema and the same multi-dialect approach established throughout.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.
Or try 50 SQL practice questions →