a workaround for the 18 digit-ish disfunction of bitand

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 19 Jul 2007 16:31:58 -0400
Message-ID: <025201c7ca43$dbca5b40$1100a8c0@rsiz.com>

SQL> r

  1 select
v*(bitand(trunc(10846370260800065548/v,0),trunc(9368617832122679304/v,0))) +

  2 bitand(mod (10846370260800065548,v) ,mod
(9368617832122679304,v)) value

  3* from break18  

                VALUE

---------------------

 09368617832122679304  

Now I suppose you want to know what "v" is.  

create table break18 (v number);

insert into break18 values (17179869184);

commit;  

Now why that value? Well, it is a power of 2 that divides 20 digits *about* in half.  

Oddly enough, and I haven't figured this out yet, if you use the full 20 digits of all 9's,  

then    

SQL> select

v*(bitand(trunc(99999999999999999999/v,0),trunc(99999999999999999999/v,0)))

+

  2            bitand(mod  (99999999999999999999,v)  ,mod


(99999999999999999999/v,0)) value

  3 from break18;  

                VALUE

---------------------

 99999999999445424011  

and I don't know what happened to the missing bits from the 4 and to the right.  

Okay, so putting a few more values into break18 we can see a loss of bits as so:  

SQL> r

  1 select

v*(bitand(trunc(99999999999999999999/v,0),trunc(99999999999999999999/v,0)))

+

  2            bitand(mod  (99999999999999999999,v)  ,mod


(99999999999999999999/v,0)) value,

  3

v*(bitand(trunc(99999999999999999999/v,0),trunc(99999999999999999999/v,0)))

value1,

  4            bitand(mod  (99999999999999999999,v)  ,mod


(99999999999999999999/v,0)) value2,


  5         v

  6 from break18

  7* order by value  

                VALUE                VALUE1                VALUE2

V

• --------------------- ---------------------

  ---

 99999999994596929302 99999999994043039744 00000000000553889558 00000000008589934592

 99999999999445424011 99999999994043039744 00000000005402384267 00000000017179869184

 99999999999463046234 99999999998338007040 00000000001125039194 00000000002147483648

 99999999999999434285 99999999998338007040 00000000001661427245 00000000004294967296  

so it looks like 4294967296 is the best value. I'm not sure how high a number will work and keep all the bits, or how the internals work.  

By now I probably should have trussed the thing.  

All these values of "v" work just fine for the example of the Original poster.  

Regards,  

mwf    

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jul 19 2007 - 15:31:58 CDT