Re: EXTRACT versur TO

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Fri, 9 Mar 2007 16:47:39 +0100
Message-ID: <45f1819a$0$28782$426a74cc@news.free.fr>

"Mark D Powell" <Mark.Powell_at_eds.com> a écrit dans le message de news: 1172762941.536318.30930_at_31g2000cwt.googlegroups.com...
| On Feb 28, 9:51 pm, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
| > Is there any performance differences between using:
| >
| > EXTRACT(MONTH FROM variable) and TO_CHAR(variable,'MM')
| >
| > or do they both perform just as efficiently as the other?
| >
| > I have some PL/SQL code where it is using the EXTRACT to compare date
| > portions of one field against date portions of another field.
| >
| > Thanks.
|
| The EXTRACT function was provided to support the new with 9i TIMESTAMP
| data types. It has limited use on traditional DATE data types being
| limited to extracting Year, Month, and Day only. So I would use
| to_char on DATE datatypes and on TIMESTAMP data types where you want
| to reformat the output and I would use EXTRACT where only a specific
| portion of the data is needed.
|
| I have not ran performance tests but I suspect that you will need
| large row counts to be able to measure the difference. Perhaps
| someone has tested and saved the results and will share them with us.
|
| HTH -- Mark D Powell --
|

SQL> declare

  2    i     pls_integer;
  3    month pls_integer;
  4    sttim pls_integer;
  5    run1  pls_integer;
  6    run2  pls_integer;

  7 begin
  8 sttim := dbms_utility.get_time;
  9 for i in 1..200000 loop
10 month := to_number(to_char(systimestamp,'MM')); 11 end loop;
12 run1 := dbms_utility.get_time - sttim; 13 sttim := dbms_utility.get_time;
14 for i in 1..200000 loop
15 month := extract(month from systimestamp); 16 end loop;
17 run2 := dbms_utility.get_time - sttim;

 18    dbms_output.put_line ('Run1 ran in ' || run1 || ' hsecs');
 19    dbms_output.put_line ('Run2 ran in ' || run2 || ' hsecs');
 20    dbms_output.put_line ('Run1 ran in ' || round(run1/run2*100,2) ||
 21                          '% of Run2 time');

22 end;
23 /
Run1 ran in 294 hsecs
Run2 ran in 244 hsecs
Run1 ran in 120.49% of Run2 time

PL/SQL procedure successfully completed.

Regards
Michel Cadot Received on Fri Mar 09 2007 - 09:47:39 CST

Re: EXTRACT versur TO_CHAR