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

Put your code in <code> and </code> tags

16 Responses to “OR aggregate”

  1. Don Burleson Says:

    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 Says:

    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.

  3. Laurent Schneider Says:

    according to the name, it seems to be an internal function used for materialized views

  4. Rob van Wijk Says:

    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 Says:

    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 Says:

    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 Says:

    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
    

  8. Rob van Wijk Says:

    Good points.

    Although the preconditions were not mentioned, your solution handles them and is therefore more robust.

    Regards,
    Rob.

  9. Aketi Jyuuzou Says:

    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;

  10. Laurent Schneider Says:

    Nice approach, I wished I found it myself :-D

  11. Aketi Jyuuzou Says:

    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;

  12. Laurent Schneider Says:

    this makes sense!

  13. Laurent Schneider Says:

    and for AGGREGATE_BITAND, we could use MIN then!

  14. Laurent Schneider Says:

    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; 
    

  15. Aketi Jyuuzou Says:

    http://oraclesqlpuzzle.hp.infoseek.co.jp/8-20.html
    My site mentions these solutions 8-) (written by Japanese language)
    Thank you :-D

  16. Laurent Schneider Says:

    wow! this is exactly the same, I like this ;-)

    arigato!

Leave a Reply

Use <code> and </code> to post code