Oracle BIT_AND_AGG Function

Sets of yes-or-no options are often stored compactly as bits of one integer: 1 for snacks, 2 for a hot meal, 4 for a bar, 8 for Wi-Fi. BIT_AND_AGG, BIT_OR_AGG, and BIT_XOR_AGG combine such integers over a group, bit by bit, to answer questions like "which services are on every flight" in one aggregate.

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:

bit_and_agg(expr)
bit_or_agg(expr)
bit_xor_agg(expr)

Combine Service Flags

Four flights have the flags 3, 7, 15, and 11.

Example:

-- service flags per route: 1 = snacks, 2 = hot meal, 4 = bar, 8 = Wi-Fi
select bit_and_agg(flags) as on_every_flight,
       bit_or_agg(flags)  as on_some_flight,
       bit_xor_agg(flags) as xor
from   (values (3), (7), (15), (11)) t (flags);

Output:

   ON_EVERY_FLIGHT    ON_SOME_FLIGHT    XOR
__________________ _________________ ______
                 3                15      0

3 is snacks plus hot meal, which every flight has, so BIT_AND_AGG returns 3. BIT_OR_AGG returns 15: every service is on some flight. Each bit is set an even number of times, so BIT_XOR_AGG returns 0.

Read the Flags with BITAND

BITAND tests single bits within one value, which shows what the flags mean.

Example:

-- decode the flags with BITAND: 1 = snacks, 2 = hot meal, 4 = bar, 8 = Wi-Fi
select flags,
       case when bitand(flags, 1) > 0 then 'Y' end as snacks,
       case when bitand(flags, 2) > 0 then 'Y' end as hot_meal,
       case when bitand(flags, 4) > 0 then 'Y' end as bar,
       case when bitand(flags, 8) > 0 then 'Y' end as wifi
from   (values (3), (7), (15), (11)) t (flags);

Output:

   FLAGS SNACKS    HOT_MEAL    BAR    WIFI
________ _________ ___________ ______ _______
       3 Y         Y
       7 Y         Y           Y
      15 Y         Y           Y      Y
      11 Y         Y                  Y

Snacks and the hot meal appear on all four flights, matching BIT_AND_AGG's 3.

Things to Know

• The values must be integers; NULLs are ignored.
• BIT_XOR_AGG is useful as a quick, order-independent fingerprint of a set of integers.
• For readability in new designs, separate BOOLEAN columns are easier to query than packed bits; bit aggregates shine with existing flag columns and compact storage.

Related Guides

• Oracle BOOLEAN_AND_AGG Function

Conclusion

BIT_AND_AGG keeps the bits set in every value of a group, BIT_OR_AGG those set in any, and BIT_XOR_AGG those set an odd number of times. Use them with flag integers to find common and available options across rows, and BITAND to decode single bits.