Performance Comparison of Different Datatypes

Dear readers,

Oracle database has a rich collection of datatypes and it offers different datatypes for different needs. Basically, datatype can be either scalar or non-scalar. A scalar type contains an atomic value, whereas a non-scalar contains a set of values. Examples of scalar datatypes include number, varchar2, etc, while that of non-scalar could be a collection.

Apart from the rich collection of data types available in Oracle database, PL/SQL offers few more datatypes like BINARY_INTEGER and PLS_INTEGER. These datatypes can be used within a PL/SQL block.

Often, these datatypes provide better performance over other and one should use them where appropriate.

I picked up couple of datatypes and performed 100,000,000 iterations to compare processing time of these datatypes. Amazingly there was a huge difference of processing time between datatypes. For some of the datatypes the processing time was 10 times lower than their counterparts.

Below is the script and its output, which I ran against Oracle Database 10g Release 2 (10.2.0.3).

set serveroutput on

declare
  l_time number;

  l_bi   BINARY_INTEGER;
  l_pi   PLS_INTEGER;
  l_bf   BINARY_FLOAT;
  l_bd   BINARY_DOUBLE;
  l_ntn  NATURALN := 0;

  l_num  NUMBER;
  l_int1 INTEGER;
  l_int2 INT;
  l_sint SMALLINT;
  l_dec1 DECIMAL;
  l_dec2 DEC;
  l_real REAL;
  l_flt  FLOAT;
  l_nrc  NUMERIC;
  l_dpr  DOUBLE PRECISION;

begin
  l_time := dbms_utility.get_time;
  dbms_output.put_line(chr(10)  chr(10) 
      'Time taken for 100,000,000 iterations for : '  chr(10)  chr(10) );

  for i in 0..99999999 loop
    l_bi := i;
  end loop;
  dbms_output.put_line(rpad('BINARY_INTEGER', 20, ' ')
                   ' = '  lpad(to_char(dbms_utility.get_time - l_time), 4, ' '));

  l_time := dbms_utility.get_time;
  for i in 0..99999999 loop
    l_pi := i;
  end loop;
  dbms_output.put_line(rpad('PLS_INTEGER', 20, ' ')
                   ' = '  lpad(to_char(dbms_utility.get_time - l_time), 4, ' '));

  l_time := dbms_utility.get_time;
  for i in 0..99999999 loop
    l_ntn := i;
  end loop;
  dbms_output.put_line(rpad('NATURALN', 20, ' ')
                   ' = '  lpad(to_char(dbms_utility.get_time - l_time), 4, ' '));

  l_time := dbms_utility.get_time;
  for i in 0..99999999 loop
    l_num := i;
  end loop;
  dbms_output.put_line(rpad('NUMBER', 20, ' ')
                   ' = '  lpad(to_char(dbms_utility.get_time - l_time), 4, ' '));

  l_time := dbms_utility.get_time;
  for i in 0..99999999 loop
    l_int1 := i;
  end loop;
  dbms_output.put_line(rpad('INTEGER', 20, ' ')
                   ' = '  lpad(to_char(dbms_utility.get_time - l_time), 4, ' '));

  l_time := dbms_utility.get_time;
  for i in 0..99999999 loop
    l_int2 := i;
  end loop;
  dbms_output.put_line(rpad('INT', 20, ' ')
                   ' = '  lpad(to_char(dbms_utility.get_time - l_time), 4, ' '));

  l_time := dbms_utility.get_time;
  for i in 0..99999999 loop
    l_sint := i;
  end loop;
  dbms_output.put_line(rpad('SMALLINT', 20, ' ')
                   ' = '  lpad(to_char(dbms_utility.get_time - l_time), 4, ' '));

  l_time := dbms_utility.get_time;
  for i in 0..99999999 loop
    l_dec1 := i;
  end loop;
  dbms_output.put_line(rpad('DECIMAL', 20, ' ')
                   ' = '  lpad(to_char(dbms_utility.get_time - l_time), 4, ' '));

  l_time := dbms_utility.get_time;
  for i in 0..99999999 loop
    l_dec2 := i;
  end loop;
  dbms_output.put_line(rpad('DEC', 20, ' ')
                   ' = '  lpad(to_char(dbms_utility.get_time - l_time), 4, ' '));

  l_time := dbms_utility.get_time;
  for i in 0..99999999 loop
    l_real := i;
  end loop;
  dbms_output.put_line(rpad('REAL', 20, ' ')
                   ' = '  lpad(to_char(dbms_utility.get_time - l_time), 4, ' '));

  l_time := dbms_utility.get_time;
  for i in 0..99999999 loop
    l_flt := i;
  end loop;
  dbms_output.put_line(rpad('FLOAT', 20, ' ')
                   ' = '  lpad(to_char(dbms_utility.get_time - l_time), 4, ' '));

  l_time := dbms_utility.get_time;
  for i in 0..99999999 loop
    l_nrc := i;
  end loop;
  dbms_output.put_line(rpad('NUMERIC', 20, ' ')
                   ' = '  lpad(to_char(dbms_utility.get_time - l_time), 4, ' '));

  l_time := dbms_utility.get_time;
  for i in 0..99999999 loop
    l_dpr := i;
  end loop;
  dbms_output.put_line(rpad('DOUBLE PRECISION', 20, ' ')
                   ' = '  lpad(to_char(dbms_utility.get_time - l_time), 4, ' '));

end;

Time taken for 100,000,000 iterations for :


BINARY_INTEGER......  172
PLS_INTEGER.........  189
BINARY_FLOAT........  611
BINARY_DOUBLE.......  473
NATURALN............  650
NUMBER.............. 1047
INTEGER............. 1839
INT................. 1841
SMALLINT............ 1842
DECIMAL............. 1933
DEC................. 1900
REAL................ 1051
FLOAT............... 1047
NUMERIC............. 1847
DOUBLE PRECISION.... 1055

PL/SQL procedure successfully completed.

As you may notice, PLS_INTEGER and BINARY_INTEGER datatypes are nearly 7 times faster than popularly used NUMBER datatype. This is because, these datatypes require less storage and they use hardware arithmetic whereas, NUMBER and INTEGER variables require calls to library routines.

Moreover, Oracle Documentation requests PL/SQL developers to avoid using INTEGER and NATURALN datatypes where performance is critical. According to the documentation, variables of these types require extra checking at run time, each time they are used in a calculation.

So, use of right datatype will really pay off in terms of performance, thus, better choose the right datatype for your operation.

References:

For more information on these datatypes and their magnitude ranges please refer to Oracle Documentation:

Use PLS_INTEGER for Integer Arithmetic.

Use BINARY_FLOAT and BINARY_DOUBLE for Floating-Point Arithmetic.

Oracle Database 10g Release 2: SQL Reference.

Oracle Database 10g Release 2: PL/SQL User’s Guide and Reference.

Happy reading !!!