In the 1990s and early 2000s, it was considered common knowledge that Oracle databases were not very good at pure number crunching. As a result, many people used C-based and other external methods to perform extensive calculations. Eventually, Oracle introduced a group of datatypes that could finally provide the required level of performance: BINARY_INTEGER, BINARY_FLOAT, and BINARY_DOUBLE. All of these datatypes use machine arithmetic operations that make them much faster.
You need to be aware that using BINARY_FLOAT and BINARY_DOUBLE has some side effects. For both of these datatypes, floating-point operations are indeed binary. Therefore, the rounding results may not be what you expect since binary (and not decimal) rounding will be used. Also, because of machine arithmetic, you may have portability issues since the same operations may differ slightly on different hardware.
Starting with Oracle Database 11g, there is an even more optimized datatype, namely SIMPLE_INTEGER, that is oriented toward running inside natively compiled PL/SQL. The following set of procedures illustrates its performance benefits:
Now these procedures will be executed, but P_TESTSIMPLEINTEGER will also be recompiled natively and executed a second time:
The test results show that BINARY_INTEGER is more than four times faster compared with NUMBER, but natively compiled SIMPLE_INTEGER cuts the total time spent down to 1/100th of a second. Overall, an improvement factor of 33 is realized. Of course, this is for a loop of 1 million operations. If your system has a very large volume of numeric operations, these micro-savings can have a significant performance impact, especially with floating-point operations.
Leave a Reply