Link: http://www.oracle-base.com/articles/11g/PlsqlNewFeaturesAndEnhancements_11gR1.php#simple_integer

The SIMPLE_INTEGER datatype is a subtype of the PLS_INTEGER datatype and can dramatically increase the speed of integer arithmetic in natively compiled code, but only shows marginal performance improvements in interpreted code. The following procedure compares the performance of the SIMPLE_INTEGER and PLS_INTEGER datatypes.

CREATE OR REPLACE PROCEDURE simple_integer_test_proc AS
l_start NUMBER;
l_loops NUMBER := 10000000;
l_pls_integer PLS_INTEGER := 0;
l_pls_integer_incr PLS_INTEGER := 1;
l_simple_integer SIMPLE_INTEGER := 0;
l_simple_integer_incr SIMPLE_INTEGER := 1;
BEGIN

l_start := DBMS_UTILITY.get_time;

FOR i IN 1 .. l_loops LOOP
l_pls_integer := l_pls_integer + l_pls_integer_incr;
END LOOP;

DBMS_OUTPUT.put_line(’PLS_INTEGER: ‘ || (DBMS_UTILITY.get_time - l_start) || ‘ hsecs’);

l_start := DBMS_UTILITY.get_time;

FOR i IN 1 .. l_loops LOOP
l_simple_integer := l_simple_integer + l_simple_integer_incr;
END LOOP;

DBMS_OUTPUT.put_line(’SIMPLE_INTEGER: ‘ || (DBMS_UTILITY.get_time - l_start) || ‘ hsecs’);

END simple_integer_test_proc;
/

When run in the default interpreted mode the performance improvement of the SIMPLE_INTEGER datatype is not spectacular.

SQL> SET SERVEROUTPUT ON
SQL> EXEC simple_integer_test_proc;
PLS_INTEGER: 47 hsecs
SIMPLE_INTEGER: 44 hsecs

PL/SQL procedure successfully completed.

SQL>

We natively compile the procedure by altering the PLSQL_CODE_TYPE value for the session and recompiling the procedure.

ALTER SESSION SET PLSQL_CODE_TYPE=NATIVE;
ALTER PROCEDURE simple_integer_test_proc COMPILE;

Natively compiling the procedure produces dramatic speed improvements for both datatypes, but more so for the SIMPLE_INTEGER datatype.

SQL> SET SERVEROUTPUT ON
SQL> EXEC simple_integer_test_proc;
PLS_INTEGER: 10 hsecs
SIMPLE_INTEGER: 2 hsecs

PL/SQL procedure successfully completed.

SQL>

The speed improvements are a result of two fundamental differences between the two datatypes. First, SIMPLE_INTEGER and PLS_INTEGER have the same range (-2,147,483,648 through 2,147,483,647), but SIMPLE_INTEGER wraps round when it exceeds its bounds, rather than throwing an error like PLS_INTEGER.

SET SERVEROUTPUT ON

DECLARE
l_simple_integer SIMPLE_INTEGER := 2147483645;
BEGIN
FOR i IN 1 .. 4 LOOP
l_simple_integer := l_simple_integer + 1;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_simple_integer, ‘S9999999999′));
END LOOP;

FOR i IN 1 .. 4 LOOP
l_simple_integer := l_simple_integer - 1;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_simple_integer, ‘S9999999999′));
END LOOP;
END;
/
+2147483646
+2147483647
-2147483648
-2147483647
-2147483648
+2147483647
+2147483646
+2147483645

PL/SQL procedure successfully completed.

SQL>

Second, SIMPLE_INTEGER can never have a NULL value, either when it is declared, or by assignment.

DECLARE
l_simple_integer SIMPLE_INTEGER;
BEGIN
NULL;
END;
/
*
ERROR at line 2:
ORA-06550: line 2, column 20:
PLS-00218: a variable declared NOT NULL must have an initialization assignment

SQL>

DECLARE
l_simple_integer SIMPLE_INTEGER := 0;
BEGIN
l_simple_integer := NULL;
END;
/
*
ERROR at line 4:
ORA-06550: line 4, column 23:
PLS-00382: expression is of wrong type
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored

SQL>

The removal of overflow and NULL checking result in a significant reduction in overhead compared to PLS_INTEGER.