Thanks for the question.

Asked: September 09, 2002 - 9:49 pm UTC

Last updated: December 07, 2021 - 4:54 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

It has been suggested to me that I use PL/SQL declarations like
PROCEDURE foo ( p_id IN PLS_INTEGER )...
instead of
PROCEDURE foo (p_id IN NUMBER ) ...
or
PROCEDURE foo (p_id IN mytable.my_id%TYPE )

I've always preferred the last option, since it keeps the code flexible with respect to the actual data. However, the person recommending PLS_INTEGER is doing so for performance. Can you comment? I'm reluctant to give up properly-engineered, lower maintenance code for the sake minuscule performance differences.

Thanks for the great site.

and Tom said...

You are right in my opinion.

PLS_INTEGER is only appropriate for performance in very localized code (it almost ALWAYS gets turned into a number anyway.

The theoretical gains are way outweighed by ease of programming, and using %type is great.

I might, *might* use pls_integer or binary_integer or some other type in some very very tight code that had to be tuned to DEATH.

Using my simple test harness:
</code> http://asktom.oracle.com/~tkyte/runstats.html <code>

we can see that simply passing the parameters results in no gain or loss:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure p1( x in pls_integer )
2 as
3 begin
4 null;
5 end;
6 /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure p2( x in number )
2 as
3 begin
4 null;
5 end;
6 /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 l_start number;
3 l_run1 number;
4 l_run2 number;
5
6 l_p1 pls_integer := 0;
7 l_p2 number := 0;
8 begin
9 insert into run_stats select 'before', stats.* from stats;
10
11 l_start := dbms_utility.get_time;
12 for i in 1 .. 1000000
13 loop
14 p1( l_p1 );
15 end loop;
16 l_run1 := (dbms_utility.get_time-l_start);
17 dbms_output.put_line( l_run1 || ' hsecs' );
18
19 insert into run_stats select 'after 1', stats.* from stats;
20 l_start := dbms_utility.get_time;
21 for i in 1 .. 1000000
22 loop
23 p2( l_p2 );
24 end loop;
25 l_run2 := (dbms_utility.get_time-l_start);
26 dbms_output.put_line( l_run2 || ' hsecs' );
27 dbms_output.put_line
28 ( 'run 1 ran in ' || round(l_run1/l_run2*100,2) || '% of the time' );
29
30 insert into run_stats select 'after 2', stats.* from stats;
31 end;
32 /
724 hsecs
723 hsecs
run 1 ran in 100.14% of the time

PL/SQL procedure successfully completed.

in 1,000,000 calls... and for many general operations

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure p1( x in out pls_integer )
2 as
3 begin
4 select count(*) into x from dual;
5 end;
6 /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure p2( x in out number )
2 as
3 begin
4 select count(*) into x from dual;
5 end;
6 /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 l_start number;
3 l_run1 number;
4 l_run2 number;
5
6 l_p1 pls_integer := 0;
7 l_p2 number := 0;
8 begin
9 insert into run_stats select 'before', stats.* from stats;
10
11 l_start := dbms_utility.get_time;
12 for i in 1 .. 100000
13 loop
14 p1( l_p1 );
15 end loop;
16 l_run1 := (dbms_utility.get_time-l_start);
17 dbms_output.put_line( l_run1 || ' hsecs' );
18
19 insert into run_stats select 'after 1', stats.* from stats;
20 l_start := dbms_utility.get_time;
21 for i in 1 .. 100000
22 loop
23 p2( l_p2 );
24 end loop;
25 l_run2 := (dbms_utility.get_time-l_start);
26 dbms_output.put_line( l_run2 || ' hsecs' );
27 dbms_output.put_line
28 ( 'run 1 ran in ' || round(l_run1/l_run2*100,2) || '% of the time' );
29
30 insert into run_stats select 'after 2', stats.* from stats;
31 end;
32 /
2288 hsecs
2266 hsecs
run 1 ran in 100.97% of the time

PL/SQL procedure successfully completed.

You'll see no measurable difference. Just to be fair -- for somethings, it can be effective:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure p1( x in out pls_integer )
2 as
3 begin
4 x := x+1;
5 end;
6 /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure p2( x in out number )
2 as
3 begin
4 x := x+1;
5 end;
6 /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 l_start number;
3 l_run1 number;
4 l_run2 number;
5
6 l_p1 pls_integer := 0;
7 l_p2 number := 0;
8 begin
9 insert into run_stats select 'before', stats.* from stats;
10
11 l_start := dbms_utility.get_time;
12 for i in 1 .. 100000
13 loop
14 p1( l_p1 );
15 end loop;
16 l_run1 := (dbms_utility.get_time-l_start);
17 dbms_output.put_line( l_run1 || ' hsecs' );
18
19 insert into run_stats select 'after 1', stats.* from stats;
20 l_start := dbms_utility.get_time;
21 for i in 1 .. 100000
22 loop
23 p2( l_p2 );
24 end loop;
25 l_run2 := (dbms_utility.get_time-l_start);
26 dbms_output.put_line( l_run2 || ' hsecs' );
27 dbms_output.put_line
28 ( 'run 1 ran in ' || round(l_run1/l_run2*100,2) || '% of the time' );
29
30 insert into run_stats select 'after 2', stats.* from stats;
31 end;
32 /
93 hsecs
171 hsecs
run 1 ran in 54.39% of the time

PL/SQL procedure successfully completed.

but in most cases, it'll be a wash, and in the case you identify as a bottleneck (using dbms_profiler for example) you would "tune" to death.

Rating

(8 ratings)

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

Comments

Thanks

Daryl, September 10, 2002 - 10:53 am UTC

I appreciate your continuing interest in doing things the "right way" (coding, bind variables, rollback space, etc.). Why is it that so many people have opinions on "performance" that can't be backed up by reality?

September 10, 2002 - 11:05 am UTC

Mystique I think.  The old "hey, i know this inside piece of information.  do it like this and you'll go faster".

If it was 100% about speed, we'd be programming assembler.  Some tightly written spaghetti coded assembler cannot be beat for raw performance.  It just takes along time and a really good coder.

There are nuggest of truth in everything -- this PLS_INTEGER for example (which cannot replace a number actually -- one thing i FORGOT to mention was that the behaviour of your PROGRAM will change!!!!!!!!

ops$tkyte@ORA920.US.ORACLE.COM> declare
  2          x pls_integer;
  3          y number;
  4  begin
  5          y := 5.232;
  6          x := y;
  7          dbms_output.put_line( y );
  8          dbms_output.put_line( x );
  9  end;
 10  /
5.232
5

PL/SQL procedure successfully completed.)  can be in some unusual cases provably faster.  (but conversely, I can prove that it is slower as well


ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure p1( x in out pls_integer )
  2  as
  3  begin
  4      x := x+1.0;
  5  end;
  6  /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure p2( x in out number )
  2  as
  3          l_var pls_integer := x;
  4  begin
  5      x := x+1.0;
  6  end;
  7  /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2      l_start number;
  3      l_run1  number;
  4      l_run2  number;
  5
  6      l_p1    pls_integer := 0;
  7      l_p2    number := 0;
  8  begin
  9      insert into run_stats select 'before', stats.* from stats;
 10
 11      l_start := dbms_utility.get_time;
 12      for i in 1 .. 100000
 13      loop
 14          p1( l_p1 );
 15      end loop;
 16      l_run1 := (dbms_utility.get_time-l_start);
 17      dbms_output.put_line( l_run1 || ' hsecs' );
 18
 19      insert into run_stats select 'after 1', stats.* from stats;
 20      l_start := dbms_utility.get_time;
 21      for i in 1 .. 100000
 22      loop
 23          p2( l_p2 );
 24      end loop;
 25      l_run2 := (dbms_utility.get_time-l_start);
 26      dbms_output.put_line( l_run2 || ' hsecs' );
 27      dbms_output.put_line
 28      ( 'run 1 ran in ' || round(l_run1/l_run2*100,2) || '% of the time' );
 29
 30      insert into run_stats select 'after 2', stats.* from stats;
 31  end;
 32  /
188 hsecs
158 hsecs
run 1 ran in 118.99% of the time

PL/SQL procedure successfully completed.


depending on what you do!)



You know -- procedures add overhead.  There is overhead in calling a procedure for a procedure.  So, should we INLINE all of the code -- and make one huge 1,000 line procedure?  Not if you want me to even consider looking at it, accepting it on my system, testing it....

it is an INTEGER after all, not a NUMBER!

Regarding PLS_INTEGER

Hemal Deshmukh, April 16, 2007 - 12:33 pm UTC

Hello Tom,
In Oracle documentation there is following sentence related to PLS_INTEGER:-
"PLS_INTEGER operations use hardware arithmetic, so they are faster than NUMBER operations, which use library arithmetic."

Can you please explain me the terms "hardware arithmetic" and "library arithmetic".
What is the difference between them.

Thanks and Best Regards
-Hemal Deshmukh

April 16, 2007 - 1:39 pm UTC

pls_integers pop numbers into registers and ask the cpu "please add these"

numbers store numeric data in a varying length character string and use a software library to emulate "adding them"

one uses a hardware instruction, the other software to emulate what the hardware would do.

Sort of like way back when we had "floating point co-processors" for PC's and the like.

PLS_INTEGER versus NUMBER versus "dynamic types"

Gaurav Khetan, July 18, 2008 - 8:33 am UTC

It has been suggested that we should index all our associative arrays using PLS_INTEGER and not NUMBER. Could you please tell me how is indexing using PLS_INTEGER more beneficial over NUMBER since we are performing no arithematic operation here.

July 18, 2008 - 5:04 pm UTC

this doesn't even make sense.

"associative arrays" are not indexed by numbers.

ops$tkyte%ORA10GR2> declare
  2          type assoc_array is table of varchar2(30) index by varchar2(2);
  3          l_state_lookup assoc_array;
  4  begin
  5          l_state_lookup('VA') := 'Virginia';
  6  end;
  7  /

PL/SQL procedure successfully completed.

and further


ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
  2          type array is table of varchar2(30) index by number;
  3  begin
  4          null;
  5  end;
  6  /
        type array is table of varchar2(30) index by number;
                      *
ERROR at line 2:
ORA-06550: line 2, column 16:
PLS-00315: Implementation restriction: unsupported table index type
ORA-06550: line 2, column 2:
PL/SQL: Item ignored

I doubt you have any indexed by numbers, you use binary_integer or pls_integer


ops$tkyte%ORA10GR2> declare
  2          type array is table of varchar2(30) index by binary_integer;
  3  begin
  4          null;
  5  end;
  6  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> declare
  2          type array is table of varchar2(30) index by pls_integer;
  3  begin
  4          null;
  5  end;
  6  /

PL/SQL procedure successfully completed.

but again, I would go to the person "suggesting" and ask them "why"

index by pls_integer

A reader, April 13, 2010 - 11:57 am UTC

"type array is table of varchar2(30) index by pls_integer;"

what does it mean ?

I have an array of varchars strings, and i am indexing it by some pl/sql integer ?

this is an un bounded array (pl/sql table) but still accessible by array index. array[i];

how is this index organized ?

and how is it a faster access?

and how the index by varchar work? now is it going to index by the "Values" of the array ?

April 14, 2010 - 7:57 am UTC

"type array is table of varchar2(30) index by pls_integer;"

what does it mean ?

I have an array of varchars strings, and i am indexing it by some pl/sql
integer ?

Yes, it reads as if it were english in this case. You have a table (collection, array) of varchar2's, the index (the subscript) to this table (array,collection) is a pls_integer type.

how is the index organized? Like a sparse array, you can have array(1), array(1000) and array(-10) filled in and nothing else - only 3 entries will consume storage. Internally - very much like a small in memory b*tree index.

how is is faster?

sometimes it is faster than X.
sometimes it is the same speed as X.
sometimes it is much much slower than X.

One would sort of need to define what X is and the entire context (conditions) surrounding X) in order to have any sort of reasonable answer to this.

and how the index by varchar work? now is it going to index by the "Values" of
the array ?

Just like an index by number type would - they are similar to associative arrays in other languages - key/value pairs. They are not arrays in the classic sense (arrays are dense, these are sparse). Internally we use a b*tree like structure in memory to organize the data - hence the "index" can be anything.

how it is going to orgaize if I don't say index by

A reader, April 14, 2010 - 10:11 am UTC

Thanks Tom for your reply.

I was thinking arrays are by default index by it's subscript. and as oracle uses sparse array b*tree way how it is organized if I don't say index by pls_integer?

only one reason I can think of is, oracle's b*tree is some kind of linked list structure and therefore no default array index and that's why we have to say specificaly "index by pls_integer" , to keep them sparse and expandable ?

April 14, 2010 - 3:46 pm UTC

... oracle's b*tree is some kind of linked list
structure...

no, they are not, not even close. They are a *tree*, not a list. They are not b*lists, they are b*trees - like a binary tree only not.

The indexes are internally used as keys, that is all.

It is organized in much the same was an index is, that is all.

It is what I said above actually.

Do not think "an array must be implemented in such a such way in all languages". I told you that index by tables are implemented internally as sparse structures - if you put index 1 in there and index 100 - that does not mean that 2, 3, 4, ... 99 come into existence (they do not). The only thing in the structure will be 1 and 100.

And they are stored in a structure that permits fast random access to any key/value pair.

If you do not say "organize by", it'll be a collection. A collection is very much like an index by table, with slightly different semantics for creating new elements. And it is not sparse.

UTL_LMS.FORMAT_MESSAGE function parameters

Alex O., June 05, 2019 - 11:33 am UTC

One more case when PLS_INTEGER usage makes the difference (seemingly, it's not too frequent): the UTL_LMS.FORMAT_MESSAGE function perfectly substitutes %d when parameters of the PLS_INTEGER type are passed, while usage of NUMBER or SOME_TABLE.SOME_FIELD%type results in empty substitutions.

June 05, 2019 - 2:06 pm UTC

Didn't know that, thanks for sharing!

Why these code shows that PLS_INTEGER is faster than NUMBER?

Waldo Araya, December 06, 2021 - 4:21 pm UTC

Hi Chris! Thank you for this great site.

I'm starting to learn PL/SQL so I'm reading PL/SQL for Dummies and I found this example with PLS_INTEGER datatype and the posibilities to improve performance, so I investigated it.

I found this articule https://oracle-base.com/articles/misc/performance-of-numeric-data-types-in-plsql which includes some tests with differents performance results than yours. I ran theses codes in apex.oracle.com with similar results.
Could you please explain why these results shows PLS_INTEGER is faster than NUMBER?. (sorry if my basic knowledge causes a basic question).
Thank you in advance and thank you for you time and knowledge.

SET SERVEROUTPUT ON
DECLARE
  l_number1          NUMBER := 1;
  l_number2          NUMBER := 1;
  l_integer1         INTEGER := 1;
  l_integer2         INTEGER := 1;
  l_pls_integer1     PLS_INTEGER := 1;
  l_pls_integer2     PLS_INTEGER := 1;
  l_binary_integer1  BINARY_INTEGER := 1;
  l_binary_integer2  BINARY_INTEGER := 1;
  l_simple_integer1  SIMPLE_INTEGER := 1;
  l_simple_integer2  SIMPLE_INTEGER := 1;
  l_loops            NUMBER := 10000000;
  l_start            NUMBER;
BEGIN
  -- Time NUMBER.
  l_start := DBMS_UTILITY.get_time;
  
  FOR i IN 1 .. l_loops LOOP
    l_number1 := l_number1 + l_number2;
  END LOOP;
  
  DBMS_OUTPUT.put_line('NUMBER         : ' ||
                       (DBMS_UTILITY.get_time - l_start) || ' hsecs');

  -- Time INTEGER.
  l_start := DBMS_UTILITY.get_time;
  
  FOR i IN 1 .. l_loops LOOP
    l_integer1 := l_integer1 + l_integer2;
  END LOOP;
  
  DBMS_OUTPUT.put_line('INTEGER        : ' ||
                       (DBMS_UTILITY.get_time - l_start) || ' hsecs');

  -- Time PLS_INTEGER.
  l_start := DBMS_UTILITY.get_time;
  
  FOR i IN 1 .. l_loops LOOP
    l_pls_integer1 := l_pls_integer1 + l_pls_integer2;
  END LOOP;
  
  DBMS_OUTPUT.put_line('PLS_INTEGER    : ' ||
                       (DBMS_UTILITY.get_time - l_start) || ' hsecs');

  -- Time BINARY_INTEGER.
  l_start := DBMS_UTILITY.get_time;
  
  FOR i IN 1 .. l_loops LOOP
    l_binary_integer1 := l_binary_integer1 + l_binary_integer2;
  END LOOP;
  
  DBMS_OUTPUT.put_line('BINARY_INTEGER : ' ||
                       (DBMS_UTILITY.get_time - l_start) || ' hsecs');

  -- Time SIMPLE_INTEGER.
  l_start := DBMS_UTILITY.get_time;
  
  FOR i IN 1 .. l_loops LOOP
    l_simple_integer1 := l_simple_integer1 + l_simple_integer2;
  END LOOP;
  
  DBMS_OUTPUT.put_line('SIMPLE_INTEGER : ' ||
                       (DBMS_UTILITY.get_time - l_start) || ' hsecs');
END;
/
NUMBER         : 24 hsecs
INTEGER        : 44 hsecs
PLS_INTEGER    : 9 hsecs
BINARY_INTEGER : 9 hsecs
SIMPLE_INTEGER : 8 hsecs

PL/SQL procedure successfully completed.

December 06, 2021 - 7:15 pm UTC

It was actually Tom who wrote the original answer!

He has several different examples - in the one that's pure PL/SQL number crunching like Tim's example you found, pls_integer did come out notably faster:

93 hsecs
171 hsecs
run 1 ran in 54.39% of the time

But this advantage is lost when you use pls_integer variables in SQL - they'll be converted to number in the process (pls_integer is a PL/SQL type).

It's also worth bearing in mind the original answer was written in 2002! While the findings hold up in for this example, this won't always be the case. Which is why posting the code used is so helpful - you can rerun the scripts yourself to see if anything's changed.

Binary vs Centesimal

PDP11, December 07, 2021 - 12:33 am UTC

why these results shows PLS_INTEGER is faster than NUMBER?

pls_integer has a 32 bit binary representation (-2^31 ... +2^31-1). Addition is performed by simple machine instruction.

Number is a variable-length packed decimals. One byte is the sign+exponent, other bytes are the mantissa, one byte per each two decimals.

 123 =xC20218 -> +0.0123*100^2
1234 =xC20D23 -> +0.1234*100^2
12.34=xC10D23 -> +0.1234*100^1

Extra processor operations are required to iterate the bytes and adjust the result.

December 07, 2021 - 4:54 pm UTC

Indeed

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

Breadcrumb

Question and Answer