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

On 10/11/07, Jared Still <jkstill_at_gmail.com> wrote:
> 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?

(snip)
> 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:
(snip)

> 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.

Different (opposite) result in SQL (10.2.0.3) - probably the SQL implementation of this functions is different from the PL/SQL one:

SQL> select max(to_date(to_char(sysdate + rownum/1000) )) from dual connect by level <= 10000000;

Elapsed: 00:02:52.94

SQL> select max(cast (to_char(sysdate + rownum/1000) as date)) from dual connect by level <= 10000000;

Elapsed: 00:03:32.10

The difference is not big, more or less is the same - cast() is less efficient tough in SQL ...

-- 
Alberto Dell'Era
"the more you know, the faster you go"
--
http://www.freelists.org/webpage/oracle-l