Questioning Oracle Documentation

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Sat, 10 Jul 2004 13:42:20 -0700
Message-ID: <1089492156.252777@yasure>

In Tuning PL/SQL Applications for Performance http://download-west.oracle.com/docs/cd/B13789_01/appdev.101/b10807/12_tune.htm#LNPLS012 I found the following and decided it could make a good demo for my students.

So here's what I wrote:

DECLARE c CHAR(5);

i PLS_INTEGER;
j PLS_INTEGER;
n NUMBER := 0;

BEGIN
   BEGIN
     i := dbms_utility.get_time();

     FOR i IN 1 .. 10000000
     LOOP
       n := n + 15;
     END LOOP;

     j := dbms_utility.get_time() - i;
     dbms_output.put_line('n+15 =     ' || TO_CHAR(j));

   END;    BEGIN
     i := dbms_utility.get_time();

     FOR i IN 1 .. 10000000
     LOOP
       n := n+15.0;
     END LOOP;

     j := dbms_utility.get_time() - i;
     dbms_output.put_line('n+15.0 =   ' || TO_CHAR(j));

   END;    BEGIN
     i := dbms_utility.get_time();

     FOR i IN 1 .. 10000000
     LOOP
       c := 15;
     END LOOP;

     j := dbms_utility.get_time() - i;
     dbms_output.put_line('c=15 =      ' || TO_CHAR(j));

   END;    BEGIN
     i := dbms_utility.get_time();

     FOR i IN 1 .. 10000000
     LOOP
       c := TO_CHAR(15);
     END LOOP;

     j := dbms_utility.get_time() - i;
     dbms_output.put_line('c=TO_CHAR = ' || TO_CHAR(j));

   END;    BEGIN
     i := dbms_utility.get_time();

     FOR i IN 1 .. 10000000
     LOOP
       c := '15';
     END LOOP;

     j := dbms_utility.get_time() - i;
     dbms_output.put_line('c=''15'' =   ' || TO_CHAR(j));

   END;
END;
/

Can anyone hazard a guess as to why I am getting the following result?

n+15      = 201
n+15.0    = 204
c=15      =  38
c=TO_CHAR = 304
c='15'    = 307

Why is the implicit conversion, that we are being warned not to do the fastest?

10.1.0.2 on Win2K w/ 1GB RAM

Daniel Morgan Received on Sat Jul 10 2004 - 15:42:20 CDT