Skip to Main Content
  • Questions
  • Are BITAND & BIT_AND functions - DETERMINISTIC functions ?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: March 10, 2016 - 7:33 pm UTC

Last updated: March 11, 2016 - 10:47 pm UTC

Version: 11.2.0.3.0, 12.1.0.2.0

Viewed 1000+ times

You Asked


Are BITAND & BIT_AND functions - DETERMINISTIC functions ?

I've a following function in my system.

FUNCTION bit_and ( Num1 IN NUMBER, Num2 IN NUMBER )
RETURN NUMBER
IS
v_digit number := 32;
Mul_Factor number;
v_Bit_And number;
begin
Mul_Factor := power(2, v_Digit);

if Num1 is null or Num2 is null then
return null;
end if;

if Num1 = 0 or Num2 = 0 then
return 0;
end if;

IF Num1 < Mul_factor and Num2 < Mul_Factor Then
return BITAND ( Num1, Num2 );
else
v_bit_and := BITAND ( mod(Num1, Mul_Factor), mod(Num2, Mul_Factor) );
return Mul_factor * bit_and(trunc(Num1/Mul_factor), trunc(Num2/Mul_factor)) + v_bit_and ;
end if;
END bit_and;

I'm planning to make it a Deterministic function. Is this a good candidate for a Deterministic function.

Thanks

and Connor said...

Deciding on determinism is easy.

1) For a given input 'n', will execution of the function always return the same result

2) Does this hold no matter what the value of 'n'.

If you've satisfied those two rules, you're good to go.

But I'm curious - why the function ? bitand works above power(2,32)

SQL> declare
  2    x number;
  3    y number;
  4    b number;
  5  begin
  6    x := power(2,40);
  7    y := power(2,45);
  8
  9    b := bitand(x,y);
 10  end;
 11  /

PL/SQL procedure successfully completed.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, March 11, 2016 - 10:48 am UTC

Thanks Connor for your answer.

This function is there in my system since 2009 and is widely used, so I'm not planning to make any changes in there. But then I made it Deterministic which helped us to improve performance.

My only concern is whether the value returns from this function is always correct or not. I did my share of testing and looks like it's giving correct answer. But there are so many scenarios to test.

By looking at function, what's you opinion ?

Also, How long does Oracle keep the value of a Deterministic function and reuse it ? Is it Session specific, or Time specific, or some thing else ?

Thanks.
Connor McDonald
March 11, 2016 - 10:47 pm UTC

Yes, I would say it is deterministic.

Deterministic results are cached in memory, and so may be aged out under memory pressure etc. And similarly, if you call the function with millions of different values, then its unlikely that all of them will remain cached.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library