OR aggregate

you want to BIT_OR multiple rows. For example you have a table with 3 rows that you want to aggregate with BIT_OR

1010 (10)
1100 (12)
0110 (6)
=========
1110 (14)

Let’s try

with t as (
select 10 n from dual union all
select 12 from dual union all
select 6 from dual)
select
utl_raw.cast_to_binary_integer(
sys.mvaggrawbitor(
utl_raw.cast_from_binary_integer(
n
)
)
) N
from t;
N
---
14

It is that easy !

disclaimer: mvaggrawbitor is not documented

16 thoughts on “OR aggregate”

  1. Don Burleson

    Hi Laurent,

    >> It is that easy !

    Ha! Maybe easy for you!

    This is very impressive and elegent solution to a complex problem! You are a worthy scholar indeed . . . .

  2. Chen Shapira

    Hi Laurent,

    Any idea why Oracle has mvaggrawbitor but no aggregated “and” and “not” operators? (At least on 10.2.0.2)
    If they only have enough space for just one aggregating binary operator, XOR would be a better option.

  4. Rob van Wijk

    or:

    SQL> with t as
    2 ( select *
    3 from mytable
    4 model
    5 dimension by (row_number() over (order by null) rn)
    6 measures (n)
    7 rules
    8 ( n[any] order by rn desc = nvl(n[cv()+1],0) + n[cv()] – bitand(nvl(n[cv()+1],0),n[cv()])
    9 )
    10 )
    11 select n
    12 from t
    13 where rn = 1
    14 /

    N
    ———-
    14

  5. Laurent Schneider Post author

    the same without subquery

    select n
    from mytable
    model
    return updated rows
    dimension by (row_number() over (order by null) rn)
    measures (n)
    rules iterate (10000)
    until (presentv(n[ITERATION_NUMBER+3],1,2)=2)
    ( n[1] = n[1] + nvl(n[ITERATION_NUMBER+2],0) -
    bitand(n[1],nvl(n[ITERATION_NUMBER+2],0)))
    /

  6. Rob van Wijk

    That’s even better.
    And doing some final (?) little optimizations:

    SQL> select n
    2 from mytable
    3 model
    4 return updated rows
    5 dimension by (rownum r)
    6 measures (n)
    7 rules iterate (10000) until n[iteration_number+3] is null
    8 ( n[1] = n[1] + n[iteration_number+2] – bitand(n[1],n[iteration_number+2])
    9 )
    10 /

    N
    ———-
    14

    Now, it really starts looking that easy 🙂

    Regards,
    Rob.

  7. Laurent Schneider Post author

    well, if the table contains only one row, it will return null and if the third row is a NULL, it will exit too early (therefore UNTIL PRESENTV)


    with mytable as (
    select 1 p, 63 n from dual union all
    select 2,1 from dual union all
    select 2,1 from dual union all
    select 2,null from dual union all
    select 2,1 from dual)
    select p,n
    from mytable
    model
    return updated rows
    partition by (p)
    dimension by (row_number() over (partition by p order by null) rn)
    measures (n)
    rules iterate (10000)
    until (presentv(n[ITERATION_NUMBER+3],1,2)=2)
    (
    n[1] = n[1] + nvl(n[ITERATION_NUMBER+2],0) -
    bitand(n[1],nvl(n[ITERATION_NUMBER+2],0))
    )
    /

    P N
    ---------- ----------
    1 63
    2 1

  9. Aketi Jyuuzou

    Oh
    Good solution!

    I post homeMadeBitOr 🙂

    with t as (
    select 10 n from dual union all
    select 12 from dual union all
    select 6 from dual)
    select
    sum(distinct(bitand(32,n)))+
    sum(distinct(bitand(16,n)))+
    sum(distinct(bitand( 8,n)))+
    sum(distinct(bitand( 4,n)))+
    sum(distinct(bitand( 2,n)))+
    sum(distinct(bitand( 1,n))) as N
    from t;

  11. Aketi Jyuuzou

    oops
    We can use solution which is more simple.

    with t as (
    select 10 n from dual union all
    select 12 from dual union all
    select 6 from dual)
    select
    max(bitand(32,n))+
    max(bitand(16,n))+
    max(bitand( 8,n))+
    max(bitand( 4,n))+
    max(bitand( 2,n))+
    max(bitand( 1,n)) as N
    from t;

  14. Laurent Schneider Post author

    for AGGREGATE_BITXOR, I would then use :
    select
    mod(sum(sign(bitand(32,n))),2)*32+
    mod(sum(sign(bitand(16,n))),2)*16+
    mod(sum(sign(bitand( 8,n))),2)* 8+
    mod(sum(sign(bitand( 4,n))),2)* 4+
    mod(sum(sign(bitand( 2,n))),2)* 2+
    mod(sum(sign(bitand( 1,n))),2) n
    from t;

Comments are closed.