DBA Hub

📋Steps in this guide1/3

Performance of Numeric Data Types in PL/SQL

This article demonstrates the relative performance of the numeric data types in PL/SQL.

oracle miscconfigurationintermediate
by OracleDba
13 views
1

NUMBER Data Type

The data type is the supertype of all numeric datatypes. It is an internal type, meaning the Oracle program performs the mathematical operations, making it slower, but extremely portable. All the tests in this article will compare the performance of other types with the data type.
2

Integer Data Types

The following code compares the performance of some integer data types with that of the data type. From this we can make a number of conclusions: There are additional predefined subtypes of , listed here . If you are dealing with an integer value, using is a safe option. It has good performance, whilst still having all the boundary checking. Only use other data types if you have a specific need they meet.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
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.

SQL>
3

Real Number Data Types

Oracle 10g introduced the and data types to handle real numbers. Both new types use machine arithmetic, making them faster than the data type, as shown in the following example. Both and out-perform the data type, but the fact they use machine arithmetic can potentially make them less portable. The same mathematical operations performed on two different underlying architectures may result in minor rounding errors. If portability is your primary concern, then you should use the type, otherwise you can take advantage of these types for increased performance. Similar to , and provide improved performance in natively compiled code because of the removal of the code path associated with NULL processing, since they can't be assigned a NULL value. If you are dealing with anything to do with finance, I would stick to using . For anything else the BINARY_* data types probably make more sense. If in doubt use . For more information see: - NUMBER - PLS_INTEGER and BINARY_INTEGER Data Types - BINARY_FLOAT - BINARY_DOUBLE Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
SET SERVEROUTPUT ON
DECLARE
  l_number1         NUMBER := 1.1;
  l_number2         NUMBER := 1.1;
  l_binary_float1   BINARY_FLOAT := 1.1;
  l_binary_float2   BINARY_FLOAT := 1.1;
  l_simple_float1   SIMPLE_FLOAT := 1.1;
  l_simple_float2   SIMPLE_FLOAT := 1.1;
  l_binary_double1  BINARY_DOUBLE := 1.1;
  l_binary_double2  BINARY_DOUBLE := 1.1;
  l_simple_double1  SIMPLE_DOUBLE := 1.1;
  l_simple_double2  SIMPLE_DOUBLE := 1.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 BINARY_FLOAT.
  l_start := DBMS_UTILITY.get_time;
  
  FOR i IN 1 .. l_loops LOOP
    l_binary_float1 := l_binary_float1 + l_binary_float2;
  END LOOP;
  
  DBMS_OUTPUT.put_line('BINARY_FLOAT   : ' ||
                       (DBMS_UTILITY.get_time - l_start) || ' hsecs');

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

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

  -- Time SIMPLE_DOUBLE.
  l_start := DBMS_UTILITY.get_time;
  
  FOR i IN 1 .. l_loops LOOP
    l_simple_double1 := l_simple_double1 + l_simple_double2;
  END LOOP;
  
  DBMS_OUTPUT.put_line('SIMPLE_DOUBLE  : ' ||
                       (DBMS_UTILITY.get_time - l_start) || ' hsecs');
END;
/
NUMBER         : 26 hsecs
BINARY_FLOAT   : 14 hsecs
SIMPLE_FLOAT   : 14 hsecs
BINARY_DOUBLE  : 15 hsecs
SIMPLE_DOUBLE  : 14 hsecs

PL/SQL procedure successfully completed.

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!