PL/SQL – choosing the BEST data type – Learning is not a spectator sport

Just a little bit of Friday fun with PL/SQL data types for this post. Let’s explore the performance differences when it comes to NUMBER, PLS_INTEGER, INTEGER, BINARY_INTEGER and SIMPLE_INTEGER.

With each data type, I’ll create a simple procedure and execute it a few times (this done on 19.15) so we can see the cost of using each data type for typical simple numeric operations.


SQL> create or replace procedure ppp is
  2    x number := 0;
  3  begin
  4    for i in  1 .. 100000000 loop
  5       x := x + 1;
  6    end loop;
  7  end;
  8  /
SQL> set timing on
SQL> exec ppp
Elapsed: 00:00:01.73
SQL> exec ppp
Elapsed: 00:00:01.78
SQL> exec ppp
Elapsed: 00:00:01.74

SQL> set timing off
SQL> create or replace procedure ppp is
  2    x pls_integer := 0;
  3  begin
  4    for i in  1 .. 100000000 loop
  5       x := x + 1;
  6    end loop;
  7  end;
  8  /
SQL> set timing on
SQL> exec ppp
Elapsed: 00:00:00.57
SQL> exec ppp
Elapsed: 00:00:00.57
SQL> exec ppp
Elapsed: 00:00:00.57

SQL> set timing off
SQL> create or replace procedure ppp is
  2    x integer := 0;
  3  begin
  4    for i in  1 .. 100000000 loop
  5       x := x + 1;
  6    end loop;
  7  end;
  8  /
SQL> set timing on
SQL> exec ppp
Elapsed: 00:00:03.72
SQL> exec ppp
Elapsed: 00:00:03.70
SQL> exec ppp
Elapsed: 00:00:03.79

SQL> set timing off
SQL> create or replace procedure ppp is
  2    x binary_integer := 0;
  3  begin
  4    for i in  1 .. 100000000 loop
  5       x := x + 1;
  6    end loop;
  7  end;
  8  /
SQL> set timing on
SQL> exec ppp
Elapsed: 00:00:00.57
SQL> exec ppp
Elapsed: 00:00:00.59
SQL> exec ppp
Elapsed: 00:00:00.58

SQL> set timing off
SQL> create or replace procedure ppp is
  2    x simple_integer := 0;
  3  begin
  4    for i in  1 .. 100000000 loop
  5       x := x + 1;
  6    end loop;
  7  end;
  8  /
SQL> set timing on
SQL> exec ppp
Elapsed: 00:00:00.56
SQL> exec ppp
Elapsed: 00:00:00.56
SQL> exec ppp
Elapsed: 00:00:00.56

Now before you race off and tell your development teams: “Hey, you need to DITCH using NUMBER and we all need to convert PLS_INTEGER!”, please take a second glance at the demo above. I am doing 100 million numeric operations, and the NUMBER datatype has a net overhead of about 1.2 seconds. I’m willing to bet that if you are doing 100 millions operations of anything (other than just incrementing a counter) then the overall elapsed time of that “anything” is going to absolutely dwarf the measly 1.2 seconds you lost by using NUMBER. As always, choose the data type that suits your functional/business requirement rather than looking for bleeding edge performance advantages unless you absolutely have to.

Most PL/SQL savvy readers probably already expected PLS_INTEGER and BINARY_INTEGER to emerge the winners here. But there are a couple of interesting things to note. Firstly, the INTEGER datatype was slower then NUMBER. This seems counter intuitive, but INTEGER is really just NUMBER in sheep’s clothing, so internally the assignment cost can be expected to the same as NUMBER, but then additionally we’ll need to ensure that every assignment to it is indeed rounded to a whole number, so I imagine that is where the extra cost comes from. Secondly, the SIMPLE_INTEGER data type is often espoused as being more efficient than any other data type when it comes to integer processing, yet in the demo above, it is no better than PLS_INTEGER. However, where SIMPLE_INTEGER really comes into its own, is when we flip the switch on the compiler and make our routines natively compiled.


SQL> alter session set plsql_code_type = native;

SQL> create or replace procedure ppp is
  2    x number := 0;
  3  begin
  4    for i in  1 .. 100000000 loop
  5       x := x + 1;
  6    end loop;
  7  end;
  8  /
SQL> set timing on
SQL> exec ppp
Elapsed: 00:00:01.35
SQL> exec ppp
Elapsed: 00:00:01.35
SQL> exec ppp
Elapsed: 00:00:01.36

SQL> set timing off
SQL> create or replace procedure ppp is
  2    x pls_integer := 0;
  3  begin
  4    for i in  1 .. 100000000 loop
  5       x := x + 1;
  6    end loop;
  7  end;
  8  /
SQL> set timing on
SQL> exec ppp
Elapsed: 00:00:00.29
SQL> exec ppp
Elapsed: 00:00:00.30
SQL> exec ppp
Elapsed: 00:00:00.29

SQL> set timing off
SQL> create or replace procedure ppp is
  2    x integer := 0;
  3  begin
  4    for i in  1 .. 100000000 loop
  5       x := x + 1;
  6    end loop;
  7  end;
  8  /
SQL> set timing on
SQL> exec ppp
Elapsed: 00:00:03.25
SQL> exec ppp
Elapsed: 00:00:03.23
SQL> exec ppp
Elapsed: 00:00:03.26

SQL> set timing off
SQL> create or replace procedure ppp is
  2    x binary_integer := 0;
  3  begin
  4    for i in  1 .. 100000000 loop
  5       x := x + 1;
  6    end loop;
  7  end;
  8  /
SQL> set timing on
SQL> exec ppp
Elapsed: 00:00:00.29
SQL> exec ppp
Elapsed: 00:00:00.30
SQL> exec ppp
Elapsed: 00:00:00.30

SQL> set timing off
SQL> create or replace procedure ppp is
  2    x simple_integer := 0;
  3  begin
  4    for i in  1 .. 100000000 loop
  5       x := x + 1;
  6    end loop;
  7  end;
  8  /
SQL> set timing on
SQL> exec ppp
Elapsed: 00:00:00.12
SQL> exec ppp
Elapsed: 00:00:00.13
SQL> exec ppp
Elapsed: 00:00:00.13

So whilst there’s a little bit of nuance to the performance of numeric operations in PL/SQL, I think you can safely assume for 99.99% of your PL/SQL usage, the selection of numeric data type on performance grounds is not going to be an issue.

One response to “PL/SQL – choosing the BEST data type”

Oracle’s Numbers | Jeff Kemp on Oracle

July 11, 2022 at 3:46 pm

[…] not be significant compared to other work your code is doing – see, for example, Connor on Choosing the Best Data Type. Choosing a data type that doesn’t use more storage than is required for your purpose can […]

Got some thoughts? Leave a comment Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Learning is not a spectator sport