Sunday, June 10, 2012

Continuing with the implicit datatype conversion topic

According to Oracle Documentation there will be implicit datatype conversion when try to assign 1 to NUMBER variable. The literal is considered as PLS_INTEGER datatype.

It is one of possible tricks for PL/SQL code tuning. Below are two sample anonymous blocks with/without implicit conversion. As the result of my tests the average execution time of the correct one (with assigning " := 1.0" ) is less then time of execution of the block with conversion.
DECLARE
   n   NUMBER := 1;
BEGIN
   FOR i IN 1..100000 LOOP
      CASE MOD(i,2)
         WHEN 0 THEN n := n + 1.0;
         WHEN 1 THEN n := n - 1.0;
      END CASE;
   END LOOP;
END;

DECLARE
   n   NUMBER := 1;
BEGIN
   FOR i IN 1..100000 LOOP
      CASE MOD(i,2)
         WHEN 0 THEN n := n + 1;
         WHEN 1 THEN n := n - 1;
      END CASE;
   END LOOP;
END;

No comments:

Post a Comment