NUMBER precision vs. performance question

From: Guang Mei <GMei_at_ph.com>
Date: Wed, 14 Jun 2006 14:20:09 -0400
Message-ID: <99965A782DFBA44EB9F773211FFC9DE602A5F62F@phexchange2.ph.com>

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

Procedure P1( p number)
Is
  x1 number :=12;
  x2 number(2,0) :=34;
  x3 BINARY_INTEGER :=56;
begin

• do something; but not do any calculations; end;

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 http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10807/12_tune.htm:

---

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. TIA.
Guang

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 14 2006 - 13:20:09 CDT