Re: CAST vs. TO_NUMBER or TO_CHAR or TO_DATE or ...

From: Jared Still <jkstill_at_gmail.com>
Date: Thu, 11 Oct 2007 09:53:56 -0700
Message-ID: <bf46380710110953y2484d65awe94a1dc98cfbc11e@mail.gmail.com>

On 10/11/07, Rumpi Gravenstein <rgravens_at_gmail.com> wrote:

>
> I've been looking at the Oracle CAST function vs. a TO_NUMBER or TO_CHAR
> or ....  Where both the CAST and TO_ functions result in equivalent answers,
> is there a preferred choice?
>
>

Hi Rumpi,

After doing a quick test with run_stats, there did not seem to be any performance advantage for one over the other.

Here were the initial tests:

   for i in 1 .. 100000
   loop

      declare
         d date;
      begin
         d := to_date('10/10/2007');
      end;

   end loop;

   for i in 1 .. 100000
   loop

      declare
         d date;
      begin
         d := cast('10/10/2007' as date);
      end;

   end loop;

The timing for these was nearly identical:

09:41:47 SQL> @th1
.309779 secs
.306977 secs

PL/SQL procedure successfully completed.

These were fairly consistent results

Then I tried something a bit different:

   declare

      d1 date := sysdate;
      d2 date;
   begin
      for i in 1 .. 100000
      loop
         d2 := to_date(to_char(d1,'mm/dd/yyyy'));
         d1 := d1 + 1;
      end loop;

   end;

   declare

      d1 date := sysdate;
      d2 date;
   begin
      for i in 1 .. 100000
      loop
         d2 := cast(to_char(d1) as date);
         d1 := d1 + 1;
      end loop;

   end;

Notice that these tests both increment the date by 1 before running the to_date or cast function.
The CAST() function shows a definite advantage:

09:43:52 SQL> @th2

.78836 secs
.438813 secs

PL/SQL procedure successfully completed.

These results were also fairly consistent.

These tests were all done on 9.2.0.8.

Switching to 10.2.0.3 on the same server was interesting:

09:49:10 ordevdb01.radisys.com - js001292_at_dv11 SQL> @th1
.001878 secs
.001881 secs

PL/SQL procedure successfully completed.

The improvements in 10g PL/SQL are readily apparent here. Test #1 is 2 orders of magnitude faster, as the result of the date calculation is always the same, and PL/SQL knows it.

Test #2 results are very close to what they were on 9i:

09:50:09 ordevdb01.radisys.com - js001292_at_dv11 SQL> @th2

.786504 secs
.391365 secs

PL/SQL procedure successfully completed.

CAST() is clearly faster than TO_DATE, and more robust as well.

There may be other advantages to using it. More research would be fun, but now it's time for 'real' work. :)

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Oct 11 2007 - 11:53:56 CDT