Pls_integer value requires "less" storage compared to Number. How to verify?
Version :11.2.0.2.0
I'd like to verify the follow assertion I'm reading again in the manual:
The PLS_INTEGER data type has these advantages over the NUMBER data type and NUMBER subtypes:
PLS_INTEGER values require less storage.
I'm looking for bytes effectively used.
I know the Oracle built-in data types and PL/SQL data types. I read again the specific sections in the documentation:
Oracle data types
PL/SQL data types
I've reviewed some useful functions: visze, dump(and so on:length,lengthb). I've searched here and in others forum/blogs, and even if it seems an obvious thing learnt in the past, I'm a little stuck.
My dummy procedure (visize function will not show any valid values for pls_integer because the reason illustrated by Tony Andrews in his answer below (note 13/07/11)
create or replace procedure TestSize
(
testPlsInteger in pls_integer
,testNumber in number
) is
sizePlsInteger pls_integer;
sizeNumber pls_integer;
FORMAT_DIM constant pls_integer := 15;
begin
select vsize(testPlsInteger), vsize(testNumber)
into sizePlsInteger, sizeNumber
from dual;
dbms_output.put_line('number:' || rpad(testPlsInteger, FORMAT_DIM)
||' PLS_INTEGER SIZE:' || rpad(sizePlsInteger, FORMAT_DIM)
|| ' NUMBER SIZE:' || sizeNumber);
end;
Test
begin
TestSize(2147483647, 2147483647);
TestSize(1, 1);
TestSize(1000000, 1000000);
TestSize(12345678, 12345678);
end;
Results
number:2147483647 PLS_INTEGER SIZE:6 NUMBER SIZE:6
number:1 PLS_INTEGER SIZE:2 NUMBER SIZE:2
number:1000000 PLS_INTEGER SIZE:2 NUMBER SIZE:2
number:12345678 PLS_INTEGER SIZE:5 NUMBER SIZE:5
-->>>>>>>>>NEW SECTION ADDED 13/07/2011
Following the documentation:
The PLS_INTEGER data type has these advantages over the NUMBER data type and NUMBER subtypes: PLS_INTEGER values require less storage.
and
The PLS_INTEGER data type stores signed integers in the range -2,147,483,648 through 2,147,483,647, represented in 32 bits.
- Documentation assertion "PLS_INTEGER values require less storage " isn't always true.
It should be true that number data type should require more space than pls_integer. Rewriting the previous procedure using a constant for pls_integer size ( 32 bits like the doc says), we can see below it isn't true.
I suppose it's due to the internal representation of the data type "number"
- But the OP was "how to verify"
Using the "great" suggestion given by Tony Andrews, and loading two different array of different sizes(pls_integer and number), it seems that:
- Using different numbers that require different/equal storage (output of the previously mentioned procedure "TestSize")
- Using varray to implement array in pl/sql
- Testing different dimension for these array 10 000, 100 000
- Large arrays of 100 000 numbers require more space than the pls_integer ones.
PS Obviously, this method is not totally accurate(like Tony wrote in his post) and collide with the output of my dummy procedure "TestSize", providing that the method to calculate the numbers size in "TestSize" (vsize for number) is correct!
FInally, I rarely use array of more than 100/200 elements in my production code, just to avoid to overload PGA. And for this dimensions the doubt remains!
Others studies I have to do to discover this "oracle", that is beyond my knowledge; but for this reason I wrote this question! :-)
Procedure using the new "Testsize" procedure.
Documentation assertion "PLS_INTEGER values require less storage " isn't always true.
create or replace procedure TestSize
(
testNumber in number
) is
sizeNumber pls_integer;
SIZE_PLS_INTEGER constant pls_integer:= 4; -- always 32 bits(4 bytes)
FORMAT_DIM constant pls_integer := 15;
FORMAT_MINOR constant varchar2(1) := '<';
FORMAT_EQUAL constant varchar2(1) := '=';
begin
select vsize(testNumber)
into sizeNumber
from dual;
dbms_output.put_line('number:' || rpad(testNumber, FORMAT_DIM)
||' PLS_INTEGER SIZE:'
|| case when (SIZE_PLS_INTEGER<sizeNumber) then
rpad(SIZE_PLS_INTEGER, FORMAT_DIM,FORMAT_MINOR)
when (SIZE_PLS_INTEGER=sizeNumber) then
rpad(SIZE_PLS_INTEGER, FORMAT_DIM,FORMAT_EQUAL)
else rpad(SIZE_PLS_INTEGER, FORMAT_DIM)
end
|| ' NUMBER开发者_运维知识库 SIZE:'
|| case when (sizeNumber<SIZE_PLS_INTEGER) then
rpad(sizeNumber,FORMAT_DIM,FORMAT_MINOR)
else rpad( sizeNumber,FORMAT_DIM)
end);
end TestSize;
Test
begin
TestSize(2147483647);
TestSize(1);
TestSize(10);
TestSize(100);
TestSize(1000);
TestSize(1000);
TestSize(100000);
TestSize(1000000);
TestSize(10000000);
TestSize(10000000);
TestSize(100000000);
TestSize(1000000000);
TestSize(1000000000);
TestSize(90000000);
TestSize(9923);
TestSize(99232);
TestSize(555555);
TestSize(12345);
TestSize(1234);
TestSize(1000001);
TestSize(20000000000);
TestSize(12345678);
TestSize(12345678);
end;
Results
number:2147483647 PLS_INTEGER SIZE:4<<<<<<<<<<<<<< NUMBER SIZE:6
number:1 PLS_INTEGER SIZE:4 NUMBER SIZE:2<<<<<<<<<<<<<<
number:10 PLS_INTEGER SIZE:4 NUMBER SIZE:2<<<<<<<<<<<<<<
number:100 PLS_INTEGER SIZE:4 NUMBER SIZE:2<<<<<<<<<<<<<<
number:1000 PLS_INTEGER SIZE:4 NUMBER SIZE:2<<<<<<<<<<<<<<
number:1000 PLS_INTEGER SIZE:4 NUMBER SIZE:2<<<<<<<<<<<<<<
number:100000 PLS_INTEGER SIZE:4 NUMBER SIZE:2<<<<<<<<<<<<<<
number:1000000 PLS_INTEGER SIZE:4 NUMBER SIZE:2<<<<<<<<<<<<<<
number:10000000 PLS_INTEGER SIZE:4 NUMBER SIZE:2<<<<<<<<<<<<<<
number:10000000 PLS_INTEGER SIZE:4 NUMBER SIZE:2<<<<<<<<<<<<<<
number:100000000 PLS_INTEGER SIZE:4 NUMBER SIZE:2<<<<<<<<<<<<<<
number:1000000000 PLS_INTEGER SIZE:4 NUMBER SIZE:2<<<<<<<<<<<<<<
number:1000000000 PLS_INTEGER SIZE:4 NUMBER SIZE:2<<<<<<<<<<<<<<
number:90000000 PLS_INTEGER SIZE:4 NUMBER SIZE:2<<<<<<<<<<<<<<
number:9923 PLS_INTEGER SIZE:4 NUMBER SIZE:3<<<<<<<<<<<<<<
number:99232 PLS_INTEGER SIZE:4============== NUMBER SIZE:4
number:555555 PLS_INTEGER SIZE:4============== NUMBER SIZE:4
number:12345 PLS_INTEGER SIZE:4============== NUMBER SIZE:4
number:1234 PLS_INTEGER SIZE:4 NUMBER SIZE:3<<<<<<<<<<<<<<
number:1000001 PLS_INTEGER SIZE:4<<<<<<<<<<<<<< NUMBER SIZE:5
number:20000000000 PLS_INTEGER SIZE:4 NUMBER SIZE:2<<<<<<<<<<<<<<
number:12345678 PLS_INTEGER SIZE:4<<<<<<<<<<<<<< NUMBER SIZE:5
number:12345678 PLS_INTEGER SIZE:4<<<<<<<<<<<<<< NUMBER SIZE:5
- The OP was "how to verify"
Using the "great" suggestion given by **Tony Andrews
Dummy procedure "TestSize2"
create or replace procedure testSize2
(
testNum in pls_integer
,maxDim in pls_integer
,secondsToWait in pls_integer
) is
type TPlsIntegers is varray(100000) of pls_integer;
type TNumbers is varray(100000) of number;
pls TPlsIntegers := TPlsIntegers();
numbers TNumbers := TNumbers();
MODULE constant varchar2(20) := 'TestSize2';
PLS_ACTION constant varchar2(20) := 'pls_integer';
NUMBER_ACTION constant varchar2(20) := 'number';
SEP constant varchar2(3) := ' - ';
begin
dbms_application_info.set_action(action_name => PLS_ACTION||SEP||testNum ||SEP||maxDim);
pls.extend(maxDim);
for cont in 1 .. maxDim
loop
pls(cont) := testNum;
end loop;
dbms_lock.sleep(seconds => secondsToWait);
-- check pga with query
dbms_application_info.set_action(action_name => NUMBER_ACTION||SEP||testNum ||SEP||maxDim);
numbers.extend(maxDim);
for cont in 1 .. maxDim
loop
numbers(cont) := testNum;
end loop;
-- check pga with query
DBMS_LOCK.sleep(secondsToWait);
end;
Test
declare
MAX_TO_WAIT constant pls_integer := 3;
MODULE constant varchar2(30) := 'testSize2';
begin
debug.disable;
dbms_application_info.set_module(MODULE, action_name => '');
testSize2(testNum => 90000000, secondsToWait => MAX_TO_WAIT, maxDim => 100);
testSize2(testNum => 90000000, secondsToWait => MAX_TO_WAIT, maxDim => 10000); --10 000
testSize2(testNum => 90000000, secondsToWait => MAX_TO_WAIT, maxDim => 100000); --100 000
testSize2(testNum => 12345, secondsToWait => MAX_TO_WAIT, maxDim => 10000); --10 000
testSize2(testNum => 12345, secondsToWait => MAX_TO_WAIT, maxDim => 100000); --100 000
testSize2(testNum => 2147483647, secondsToWait => MAX_TO_WAIT, maxDim => 100);
testSize2(testNum => 2147483647, secondsToWait => MAX_TO_WAIT, maxDim => 10000); --10 000
testSize2(testNum => 2147483647, secondsToWait => MAX_TO_WAIT, maxDim => 100000); --100 000*\*\*/
dbms_application_info.set_action(action_name => 'END');
end;
Query to monitor PGA
select vsst.sid || ',' || vses.serial# username, vsst.value,vses.MODULE,vses.ACTION
from v$sesstat vsst, v$statname vstt, v$session vses
where vstt.statistic# = vsst.statistic#
and vsst.sid = vses.sid
and vstt.name = 'session pga memory'
and vses.username = 'HR'
and vses.MODULE = 'testSize2'
Annoted Results
pls_integer number dif
-- size size size
-- n=90000000 DIM aRRAY= 100 528612 594148< DIF= 65536
-- n=90000000 DIM aRRAY= 10000 725220 1118436< DIF= 393216
-- n=90000000 DIM aRRAY= 100000 2101476 5771492> DIF= 3670016
-- n=12345 DIM aRRAY= 10000 921828 1380580< DIF= 458752
-- n=12345 DIM aRRAY= 100000 2101476 5771492> DIF= 3670016
-- n=2147483647 DIM aRRAY= 100 790756 856292 < DIF= 65536
-- n=2147483647 DIM aRRAY= 10000 921828 1380580< DIF= 458752
-- n=2147483647 DIM aRRAY= 100000 2101476 5771492> DIF= 3670016
The VSIZE function will not show any difference because it is an overloaded function that can take a NUMBER, DATE or VARCHAR2 value only (see the spec of SYS.STANDARD). So when you pass it a PLS_INTEGER value that value is implicitly converted to NUMBER first.
The definition of PLS_INTEGER says
The PLS_INTEGER data type stores signed integers in the range -2,147,483,648 through 2,147,483,647, represented in 32 bits.
So it is always 32 bits = 4 bytes. Whereas, as you showed, the VSIZE() of a NUMBER can be more than 4 bytes:
SQL> select vsize(2147483647) v from dual;
v
-
6
One way you may be able to see a difference is if you run some PL/SQL that populates a large array of NUMBERS in memory, and look at the PGA usage for your session before an after. Then repeat for PLS_INTEGER. You could repeat the experiment with small and large values and so on. (Caveat: PGA is allocated in chunks of a certain size, so this will not be totally accurate, but a large enough array may show any significant difference.)
To monitor PGA usage use a query like:
select vsst.sid||','||vses.serial# username,
vsst.value
from v$sesstat vsst, v$statname vstt, v$session vses
where vstt.statistic# = vsst.statistic#
and vsst.sid = vses.sid
and vstt.name = 'session pga memory'
and vses.username = 'MYUSER';
加载中,请稍侯......
精彩评论