NUMBER precision vs. performance question

  • From: "Guang Mei" <GMei@xxxxxx>
  • To: "Oracle-L \(E-mail\)" <Oracle-L@xxxxxxxxxxxxx>
  • Date: Wed, 14 Jun 2006 14:20:09 -0400

Let's say in Oracle 8i, 9i or 10g we have a proc like

Procedure P1( p  number)
  x1   number :=12;
  x2   number(2,0) :=34;
  x3   BINARY_INTEGER :=56;
  -- do something; but not do any calculations;

When the procedure is called, and x1, x2 and x3 are allocated in memory, does 
x1 take more memory space than x2, how about x2 and x3?

According to Oracle 10g Doc

When you need to declare a local integer variable, use the datatype 
PLS_INTEGER, which is the most efficient integer type. PLS_INTEGER values 
require less storage than INTEGER or NUMBER values, and PLS_INTEGER operations 
use machine arithmetic.
The BINARY_INTEGER datatype is just as efficient as PLS_INTEGER for any new 
code, but if you are running the same code on Oracle9i or Oracle8i databases, 
PLS_INTEGER is faster.
The datatype NUMBER and its subtypes are represented in a special internal 
format, designed for portability and arbitrary scale and precision, not 
performance. Even the subtype INTEGER is treated as a floating-point number 
with nothing after the decimal point. Operations on NUMBER or INTEGER variables 
require calls to library routines.
Avoid constrained subtypes such as INTEGER, NATURAL, NATURALN, POSITIVE, 
POSITIVEN, and SIGNTYPE in performance-critical code. Variables of these types 
require extra checking at run time, each time they are used in a calculation.


The issue is that I found many palces in our application code have local 
variables defined as  NUMBER, not NUMBER(p,s) or BINARY_INTEGER  , I assume the 
memory impact would be very very small, but if hundreds or thousands of places 
are like that, and the application is called by many, many users, then what 
kinds of impact could it be? BTW, I have never done any tests, just curious 
what other people's experience is.

Other related posts:

  • » NUMBER precision vs. performance question