G-2230: Try to use SIMPLE_INTEGER datatype when appropriate.

Minor

Efficiency

Restriction

ORACLE 11g or later

Reason

SIMPLE_INTEGER does no checks on numeric overflow, which results in better performance compared to the other numeric datatypes.

With ORACLE 11g, the new data type SIMPLE_INTEGER has been introduced. It is a sub-type of PLS_INTEGER and covers the same range. The basic difference is that SIMPLE_INTEGER is always NOT NULL. When the value of the declared variable is never going to be null then you can declare it as SIMPLE_INTEGER. Another major difference is that you will never face a numeric overflow using SIMPLE_INTEGER as this data type wraps around without giving any error. SIMPLE_INTEGER data type gives major performance boost over PLS_INTEGER when code is compiled in NATIVE mode, because arithmetic operations on SIMPLE_INTEGER type are performed directly at the hardware level.

Example (bad)

create or replace package body constants is
   k_big_increase constant number(1,0) := 1;

   function big_increase return number is
   begin
      return co_big_increase;
   end big_increase;
end constants;
/

Example (good)

create or replace package body constants is
   k_big_increase constant simple_integer := 1;

   function big_increase return simple_integer is
   begin
      return co_big_increase;
   end big_increase;
end constants;
/