Looking at examples online, it seems that pls_integer is beneficial for arithmetics with other pls_integers only. Is it more performant than number in arithmetics with other data types, such as date?
Running tests like these (Oracle 19.0.0):
declare
i integer;
v_date_r date;
v_months /*pls_integer*/ number := 7;
v_days /*pls_integer*/ number := 98;
begin
for i in 1..10000000 loop
v_date_r := add_months(trunc(localtimestamp),-v_months) + v_days;
end loop;
end;
declare
i integer;
v_date_r date;
v_days /*pls_integer*/ number := 322;
begin
for i in 1..10000000 loop
v_date_r := sysdate + v_days;
end loop;
end;
the tests with pls_integer can often outperform tests with number.
Why?
Isn't pls_integer converted to number to calculate sysdate + v_days? My understanding is that it is converted, so how can it be faster?
Since add_months() accepts integer, it makes sense that the function would be faster when a pls_integer is passed in rather than number, even though it then also adds v_days to the result.
Oracle documentation isn't very clear about this.
The sysdate, localtimestamp, add_months, etc.. functions will take a lot more time calling the OS to get the time or doing complex date manipulations than simple arithmetic operations on numbers, so you can't really test pls_integer vs. number performance unless you construct a test that eliminates those expensive operations. And passing them pls_integer when their argument list expects number (unless they have specific overloads for pls_integer) won't help as Oracle would immediately convert them implicitly to the datatype expected.
number is an artificial datatype constructed on a higher level than a binary integer, and therefore arithmetic on numbers requires more steps in higher-level code than bitwise operations on a binary integer does. But rarely do you have a piece of PL/SQL code that lacks other operations that are astronomically more expensive than these, so rarely does choosing pls_integer over number make a noticeable difference. You'd have to have something that loops an insane number of times and does nothing but math within it, like this:
declare
j number;
v_months number := 7;
v_days number := 98;
st timestamp with time zone;
begin
st := systimestamp;
for i in 1..100000000 loop
j := GREATEST(i,5+v_months+v_days);
end loop;
dbms_output.put_line(systimestamp - st);
end;
On my system, that resulted in +000000000 00:00:04.931385000. Compare pls_integer:
declare
j pls_integer;
v_months pls_integer := 7;
v_days pls_integer := 98;
st timestamp with time zone;
begin
st := systimestamp;
for i in 1..100000000 loop
j := GREATEST(i,5+v_months+v_days);
end loop;
dbms_output.put_line(systimestamp - st);
end;
Result: +000000000 00:00:01.794718000, just shy of 3x faster. The same can be seen by simply testing the loop iterator itself. Compare:
declare
i number := 0;
st timestamp with time zone;
begin
st := systimestamp;
while i < 100000000 loop
i := i + 1;
end loop;
dbms_output.put_line(systimestamp - st);
end;
--> +000000000 00:00:03.044420000
declare
i pls_integer := 0;
st timestamp with time zone;
begin
st := systimestamp;
while i < 100000000 loop
i := i + 1;
end loop;
dbms_output.put_line(systimestamp - st);
end;
--> +000000000 00:00:01.017006000
So pls_integer is definitely faster for arithmetic operations. But how often will anybody write PL/SQL that does nothing but math in a loop with this many iterations? And with a hundred million iterations I only saved 2 seconds. It would be a very rare case where it actually made enough of a difference to be worth being too particular about using it over number.