a workaround for the 18 digit-ish disfunction of bitand

From: "Mark W. Farnham" <mwf@xxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Thu, 19 Jul 2007 16:31:58 -0400

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

a workaround for the 18 digit-ish disfunction of bitand

Other related posts: