Home » SQL & PL/SQL » SQL & PL/SQL » pls_integer

Re: pls_integer [message #217930 is a reply to message #217873] 
Tue, 06 February 2007 02:48 
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...

Senior Member 


I have always understood that calculations involving pls_integer datatypes will be performed using the server's own Os level maths libraries, rather than Oracles, and that they will be quicker as a result.
I can't guarantee why, but some empiracle testing suggests that calculations involving pls_integer as opposed to Number types are faster by the following ratios
Operation Pls_integer time as % of Number time
Addition 65%
Addition 65%
Multiplication 65%
Division 150% (hey  it's an integer. not much call for divison there
POWER 110%



Re: pls_integer [message #217937 is a reply to message #217873] 
Tue, 06 February 2007 02:56 
chinmay_uce02
Messages: 11 Registered: January 2007

Junior Member 


Benefits of Using PLS_INTEGER Datatype in PL/SQL
If you have a wholenumber counter, for example in a loop or record counter, consider using a datatype of PLS_INTEGER instead of INTEGER or NUMBER. When declaring an integer variable, PLS_INTEGER is the most efficient numeric datatype because its values require less storage than INTEGER or NUMBER values, which are represented internally as 22byte Oracle numbers. Also, PLS_INTEGER operations use machine arithmetic, so they are faster than BINARY_INTEGER, INTEGER, or NUMBER operations, which use library arithmetic.
Variables declared as pls_integer and binary_integer have the same range, but they are treated differently. When a calculation involving pls_integer overflows, pl/sql raises an exception. However, similar overflow involving binary_integers will not raise an exception if the result is being assigned to a number variable.



Goto Forum:
Current Time: Tue Mar 28 20:37:27 CDT 2017
Total time taken to generate the page: 0.14364 seconds
