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.
oracle 21cconfigurationintermediate
by OracleDba
12 views
📋Steps in this guide1/7
1
Basics
You can find an introduction to bitwise operations here .
Here are some features common to all three functions.
- The , and 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 data type.
- Support values are from "-2 raised to 127" to "(2 raised to 127)-1".
2
Setup
The following table is used by the examples in this article.
Code/Command (click line numbers to comment):
12345678910111213141516171819202122232425
-- 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;3
BIT_AND_AGG, BIT_OR_AGG and BIT_XOR_AGG as Aggregate Functions
The following query uses the , and aggregate functions to perform bitwise operations on the column.
We can get more granularity of information by including a clause. This time we perform the bitwise operations separately for each value of the column.
Using or keywords mean only unique values in the expression are used for the bitwise operation. The keyword is that same as the default action.
The , and keywords are also available for the analytic functions.
Code/Command (click line numbers to comment):
1234567891011121314151617181920212223242526272829303132333435363738394041
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>
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>
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>4
BIT_AND_AGG Analytic Function
Using an empty clause turns the 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 analytic function to display the row data, and the bit operation of the column for all the rows in the table.
Adding the partitioning clause allows us to display the bit operation for each distinct value of the column.
Code/Command (click line numbers to comment):
123456789101112131415161718192021222324252627282930313233343536373839404142434445
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>
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>5
BIT_OR_AGG Analytic Function
Using an empty clause turns the 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 analytic function to display the row data, and the bit operation of the column for all the rows in the table.
Adding the partitioning clause allows us to display the bit operation for each distinct value of the column.
Code/Command (click line numbers to comment):
123456789101112131415161718192021222324252627282930313233343536373839404142434445
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>
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>6
BIT_XOR_AGG Analytic Function
Using an empty clause turns the 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 analytic function to display the row data, and the bit operation of the column for all the rows in the table.
Adding the partitioning clause allows us to display the bit operation for each distinct value of the column.
Code/Command (click line numbers to comment):
123456789101112131415161718192021222324252627282930313233343536373839404142434445
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>
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>7
Quick Links
The "*" indicates the function supports the full analytic syntax, including the windowing clause.
For more information see:
- BIT_AND_AGG
- BIT_OR_AGG
- BIT_XOR_AGG
- Analytic Functions : All Articles
Hope this helps. Regards Tim...
Comments (0)
Please to add comments
No comments yet. Be the first to comment!