Question: What is the pls_integer, and how
do I use pls_integer in PL/SQL.
Answer: For integer values, the PLS_INTEGER type gives
better performance and requires less storage space than either INTEGER
or NUMBER types. The PLS_INTEGER type uses machine arithmetic,
while NUMBER and its subtypes are represented in a special internal
format which requires additional calls to library routines during
operations. In addition, constrained types like INTEGER require
extra runtime checks making them even less efficient.
Also see
PLS Integer versus Number.
It is a good practice to use PLS_INTEGER versus NUMBER. The
PLS_INTEGER data Type was introduced in PL/SQL version 2.2 and has a
range of -2147483647 to 2147483647.
Use of the PLS_INTEGER data type in PL/SQL
involves less internal instructions to process, thus increasing
performance. The PLS_INTEGER is useful for counters and integer
operations
An example use of the PLS_INTEGER would be:
DECLARE
lv_temp_cond_num PLS_INTEGER := 10;
BEGIN
IF lv_temp_cond_num = 1 THEN
The BINARY_INTEGER and PLS_INTEGER types give
similar performance in Oracle 10g, but PLS_INTEGER is the preferred
type when using Oracle 8i and 9i.
The integer_test.sql script compares the
performance of the NUMBER, INTEGER, PLS_INTEGER and BINARY_INTEGER
types.
integer_test.sql
SET
SERVEROUTPUT ON
DECLARE
l_number1 NUMBER :=
1;
l_number2 NUMBER :=
1;
l_integer1 INTEGER := 1;
l_integer2 INTEGER := 1;
l_pls_integer1 PLS_INTEGER := 1;
l_pls_integer2 PLS_INTEGER := 1;
l_binary_integer1 BINARY_INTEGER := 1;
l_binary_integer2 BINARY_INTEGER := 1;
l_loops
NUMBER := 10000000;
l_start
NUMBER;
BEGIN
-- Time NUMBER.
l_start := DBMS_UTILITY.get_time;
FOR i
IN 1 .. l_loops LOOP
l_number1 := l_number1 + l_number2;
END LOOP;
DBMS_OUTPUT.put_line('NUMBER
: ' ||
(DBMS_UTILITY.get_time - l_start));
--
Time INTEGER.
l_start := DBMS_UTILITY.get_time;
FOR i
IN 1 .. l_loops LOOP
l_integer1 := l_integer1 + l_integer2;
END LOOP;
DBMS_OUTPUT.put_line('INTEGER
: ' ||
(DBMS_UTILITY.get_time - l_start));
--
Time PLS_INTEGER.
l_start := DBMS_UTILITY.get_time;
FOR i
IN 1 .. l_loops LOOP
l_pls_integer1 := l_pls_integer1 + l_pls_integer2;
END LOOP;
DBMS_OUTPUT.put_line('PLS_INTEGER : ' ||
(DBMS_UTILITY.get_time - l_start));
--
Time BINARY_INTEGER.
l_start := DBMS_UTILITY.get_time;
FOR i
IN 1 .. l_loops LOOP
l_binary_integer1 := l_binary_integer1 + l_binary_integer2;
END LOOP;
DBMS_OUTPUT.put_line('BINARY_INTEGER : ' ||
(DBMS_UTILITY.get_time - l_start));
END;
/
The results of the script run against
different versions of Oracle are listed below. Ignore the
comparison between versions as each is on a different server.
Instead concentrate on the relative performance within a single
version.
SQL> --
Oracle 8i
SQL> @integer_test.sql
NUMBER : 3037
INTEGER : 5002
PLS_INTEGER : 2664
BINARY_INTEGER : 8511
PL/SQL
procedure successfully completed.
SQL>
SQL> --
Oracle 9i
SQL> @integer_test.sql
NUMBER : 652
INTEGER : 1006
PLS_INTEGER : 511
BINARY_INTEGER : 1285
PL/SQL
procedure successfully completed.
SQL>
SQL> --
Oracle 10g
SQL> @integer_test.sql
NUMBER : 191
INTEGER : 376
PLS_INTEGER : 88
BINARY_INTEGER : 93
PL/SQL
procedure successfully completed.
From these results, it is clear that
PLS_INTEGER types are the most efficient types to use in all versions
of Oracle. It can also be said that NUMBER types are always more
efficient than INTEGER types. In Oracle 10g, the BINARY_INTEGER
type is almost as efficient and the PLS_INTEGER type, but in previous
versions it is the worst performing type.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
