Performance Improvements with Oracle 11g Data Types

Numeric operations in Oracle are a great opportunity for performance improvement. Oracle 11g provides a new data type that will reduce some of the overhead otherwise found using PL/SQL numbers. The performance improvement can be experienced when using one of the three new number data types:

• SIMPLE_INTEGER is a subtype of pls_integer
• SIMPLE_DOUBLE is a subtype of the BINARY _DOUBLE
• SIMPLE_FLOAT is a subtype of the BINARY_DOUBLE data type

The new data types provide substantial performance benefits from two areas when performing numeric computations:

1. There is no null checking, and thus the new data type cannot have a null value under any circumstances.

To demonstrate the effect of a null value in this data type which does not allow nulls:

declare
 mysalary simple_integer := 1000000;
 begin
 mysalary := null;
 end;
 /
 

ERROR at line 4:
 ORA-06550: line 4, column 14:
 PLS-00382: expression is of wrong type
 ORA-06550: line 4, column 2:
 PL/SQL: Statement ignored

2. There is no bounds checking.

For instance, the values accepted for the SIMPLE_INTEGER are the same as for the pls_integer, -2147483648 to 2147483647 but if your arithmetic computations generate a number larger than 2147483647 it will wrap around to the negative number.

For example, when testing the numeric overflow, we can easily demonstrates that adding numbers above the high value will wrap to a negative number:

declare
 mysalary simple_integer := 2147483644;
 begin
 for I in 1 .. 3 loop
 mysalary := mysalary + 3;
 dbms_output.put_line(to_char(mysalary, ‘S9999999999?));
 end loop;
 end;
 /
 +2147483647
 -2147483646
 -2147483643

In order to benefit from these new data types, natively compiled code will need to be used. This will give an additional performance boost because the arithmetic operations can occur at the hardware level.

Caution should be observed when using these data types since there is no bounds checking and if you are unaware of the scope of use the ability to go beyond the bounds of the numbers allowed will generate undesired results. But if you are fully aware of the scope of use, natively compiled code exercising numeric computations can result in significant performance improvements as a result of removing the null and overflow checking.