8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
BIT_AND_AGG, BIT_OR_AGG and BIT_XOR_AGG Analytic Functions
This article gives an overview of the BIT_AND_AGG
, BIT_OR_AGG
and BIT_XOR_AGG
analytic functions, introduced in Oracle database 21c. If you are new to analytic functions you should probably read this introduction to analytic functions first.
- Basics
- Setup
- BIT_AND_AGG, BIT_OR_AGG and BIT_XOR_AGG as Aggregate Functions
- BIT_AND_AGG Analytic Function
- BIT_OR_AGG Analytic Function
- BIT_XOR_AGG Analytic Function
- Quick Links
Related articles.
Basics
You can find an introduction to bitwise operations here.
Here are some features common to all three functions.
- The
BIT_AND_AGG
,BIT_OR_AGG
andBIT_XOR_AGG
functions can be used as aggregate functions or analytic functions. - Input expressions with a NULL value are ignored.
- The functions return NULL if all rows in the group/partition contain a NULL value for the expression.
- All values of the expression are truncated to integers prior to the application of the aggregate or analytic function.
- The expression values are converted to a 128b decimals before the operation is applied, with negative numbers represented in two's complement form. The resulting values are converted back to an Oracle
NUMBER
data type. - Support values are from "-2 raised to 127" to "(2 raised to 127)-1".
Setup
The following table is used by the examples in this article.
-- drop table t1 purge; create table t1 ( id number, code varchar2(10), string_value varchar2(10), bin_value number ); insert into t1 values (1 , 'one', '1010', bin_to_num(1,0,1,0)); insert into t1 values (2 , 'one', '0101', bin_to_num(0,1,0,1)); insert into t1 values (3 , 'two', '1000', bin_to_num(1,0,0,0)); insert into t1 values (4 , 'two', '0100', bin_to_num(0,1,0,0)); insert into t1 values (5 , 'two', '0010', bin_to_num(0,0,1,0)); insert into t1 values (6 , 'three', '0001', bin_to_num(0,0,0,1)); insert into t1 values (7 , 'three', '0010', bin_to_num(0,0,1,0)); insert into t1 values (8 , 'three', '0100', bin_to_num(0,1,0,0)); insert into t1 values (9 , 'three', '0100', bin_to_num(0,1,0,0)); insert into t1 values (10, 'four', '1000', bin_to_num(1,0,0,0)); insert into t1 values (11, 'four', '1000', bin_to_num(1,0,0,0)); commit;
BIT_AND_AGG, BIT_OR_AGG and BIT_XOR_AGG as Aggregate Functions
The following query uses the BIT_AND_AGG
, BIT_OR_AGG
and BIT_XOR_AGG
aggregate functions to perform bitwise operations on the BIN_VALUE
column.
select bit_and_agg(bin_value) as bit_and_agg_value, bit_or_agg(bin_value) as bit_or_agg_value, bit_xor_agg(bin_value) as bit_xor_agg_value from t1; BIT_AND_AGG_VALUE BIT_OR_AGG_VALUE BIT_XOR_AGG_VALUE ----------------- ---------------- ----------------- 0 15 2 SQL>
We can get more granularity of information by including a GROUP BY
clause. This time we perform the bitwise operations separately for each value of the CODE
column.
select code, bit_and_agg(bin_value) as bit_and_agg_value, bit_or_agg(bin_value) as bit_or_agg_value, bit_xor_agg(bin_value) as bit_xor_agg_value from t1 group by code order by code; CODE BIT_AND_AGG_VALUE BIT_OR_AGG_VALUE BIT_XOR_AGG_VALUE ---------- ----------------- ---------------- ----------------- four 8 8 0 one 0 15 15 three 0 7 3 two 0 14 14 SQL>
Using DISTINCT
or UNIQUE
keywords mean only unique values in the expression are used for the bitwise operation. The ALL
keyword is that same as the default action.
select bit_xor_agg(bin_value) as bit_xor_agg_value, bit_xor_agg(all bin_value) as bit_xor_agg_all_value, bit_xor_agg(distinct bin_value) as bit_xor_agg_distinct_value, bit_xor_agg(unique bin_value) as bit_xor_agg_unique_value from t1; BIT_XOR_AGG_VALUE BIT_XOR_AGG_ALL_VALUE BIT_XOR_AGG_DISTINCT_VALUE BIT_XOR_AGG_UNIQUE_VALUE ----------------- --------------------- -------------------------- ------------------------ 2 2 0 0 SQL>
The DISTINCT
, UNIQUE
and ALL
keywords are also available for the analytic functions.
BIT_AND_AGG Analytic Function
Using an empty OVER
clause turns the BIT_AND_AGG
function into an analytic function. The lack of a partitioning clause means the whole result set is treated as a single partition. The following query uses the BIT_AND_AGG
analytic function to display the row data, and the bit AND
operation of the BIN_VALUE
column for all the rows in the T1
table.
select id, code, bin_value, bit_and_agg(bin_value) over () as bit_and_agg_value from t1 order by id; ID CODE BIN_VALUE BIT_AND_AGG_VALUE ---------- ---------- ---------- ----------------- 1 one 10 0 2 one 5 0 3 two 8 0 4 two 4 0 5 two 2 0 6 three 1 0 7 three 2 0 8 three 4 0 9 three 4 0 10 four 8 0 11 four 8 0 SQL>
Adding the partitioning clause allows us to display the bit AND
operation for each distinct value of the CODE
column.
select id, code, bin_value, bit_and_agg(bin_value) over (partition by code) as bit_and_agg_value from t1 order by id; ID CODE BIN_VALUE BIT_AND_AGG_VALUE ---------- ---------- ---------- ----------------- 1 one 10 0 2 one 5 0 3 two 8 0 4 two 4 0 5 two 2 0 6 three 1 0 7 three 2 0 8 three 4 0 9 three 4 0 10 four 8 8 11 four 8 8 SQL>
BIT_OR_AGG Analytic Function
Using an empty OVER
clause turns the BIT_OR_AGG
function into an analytic function. The lack of a partitioning clause means the whole result set is treated as a single partition. The following query uses the BIT_OR_AGG
analytic function to display the row data, and the bit OR
operation of the BIN_VALUE
column for all the rows in the T1
table.
select id, code, bin_value, bit_or_agg(bin_value) over () as bit_or_agg_value from t1 order by id; ID CODE BIN_VALUE BIT_OR_AGG_VALUE ---------- ---------- ---------- ---------------- 1 one 10 15 2 one 5 15 3 two 8 15 4 two 4 15 5 two 2 15 6 three 1 15 7 three 2 15 8 three 4 15 9 three 4 15 10 four 8 15 11 four 8 15 SQL>
Adding the partitioning clause allows us to display the bit OR
operation for each distinct value of the CODE
column.
select id, code, bin_value, bit_or_agg(bin_value) over (partition by code) as bit_or_agg_value from t1 order by id; ID CODE BIN_VALUE BIT_OR_AGG_VALUE ---------- ---------- ---------- ---------------- 1 one 10 15 2 one 5 15 3 two 8 14 4 two 4 14 5 two 2 14 6 three 1 7 7 three 2 7 8 three 4 7 9 three 4 7 10 four 8 8 11 four 8 8 SQL>
BIT_XOR_AGG Analytic Function
Using an empty OVER
clause turns the BIT_XOR_AGG
function into an analytic function. The lack of a partitioning clause means the whole result set is treated as a single partition. The following query uses the BIT_XOR_AGG
analytic function to display the row data, and the bit XOR
operation of the BIN_VALUE
column for all the rows in the T1
table.
select id, code, bin_value, bit_xor_agg(bin_value) over () as bit_xor_agg_value from t1 order by id; ID CODE BIN_VALUE BIT_XOR_AGG_VALUE ---------- ---------- ---------- ----------------- 1 one 10 2 2 one 5 2 3 two 8 2 4 two 4 2 5 two 2 2 6 three 1 2 7 three 2 2 8 three 4 2 9 three 4 2 10 four 8 2 11 four 8 2 SQL>
Adding the partitioning clause allows us to display the bit XOR
operation for each distinct value of the CODE
column.
select id, code, bin_value, bit_xor_agg(bin_value) over (partition by code) as bit_xor_agg_value from t1 order by id; ID CODE BIN_VALUE BIT_XOR_AGG_VALUE ---------- ---------- ---------- ----------------- 1 one 10 15 2 one 5 15 3 two 8 14 4 two 4 14 5 two 2 14 6 three 1 3 7 three 2 3 8 three 4 3 9 three 4 3 10 four 8 0 11 four 8 0 SQL>
Quick Links
The "*" indicates the function supports the full analytic syntax, including the windowing clause.
For more information see:
Hope this helps. Regards Tim...