Re: CAST vs. TO_NUMBER or TO_CHAR or TO_DATE or ...
- From: "Alberto Dell'Era" <alberto.dellera@xxxxxxxxx>
- To: jkstill@xxxxxxxxx
- Date: Sat, 13 Oct 2007 21:24:27 +0200
On 10/11/07, Jared Still <jkstill@xxxxxxxxx> wrote:
> On 10/11/07, Rumpi Gravenstein <rgravens@xxxxxxxxx> 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@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"
--
//www.freelists.org/webpage/oracle-l
Other related posts:
