Extract Year/Month

Sometimes we have to get a year/month/day from a date. Normally I would use to_char for that, but today a friend told me about the extract() function.
I had to verify if there was some performance improvement (readability is better).
Of course for only an unique call there is no significant difference but for 200000 calls I got a 40% improve on speed.



Here is the code I used to test

DECLARE
l_char VARCHAR2 (10);
l_time NUMBER;
l_dur1 NUMBER;
l_dur2 NUMBER;
l_cicles INTEGER           := 200000;
BEGIN
l_time := DBMS_UTILITY.get_time;

FOR i IN 1 .. l_cicles
LOOP
l_char := TO_CHAR ( (SYSDATE + i), 'MM');
END LOOP;

l_dur1 := DBMS_UTILITY.get_time - l_time;
DBMS_OUTPUT.put_line ('to_char :' || TO_CHAR (l_dur1 / 100) || ' s');
l_time := DBMS_UTILITY.get_time;

FOR i
IN 1 .. l_cicles
LOOP
l_char := EXTRACT (MONTH FROM (SYSDATE + i) );
END LOOP;

l_dur2 := DBMS_UTILITY.get_time - l_time;
DBMS_OUTPUT.put_line ('EXTRACT :' || TO_CHAR (l_dur2 / 100) || ' s');
DBMS_OUTPUT.put_line (   'Extract took less '
|| TO_CHAR (100 - (l_dur2 * 100) / l_dur1)
|| ' %'
);
END;

EXTRACT (
{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
| { TIMEZONE_HOUR | TIMEZONE_MINUTE }
| { TIMEZONE_REGION | TIMEZONE_ABBR }
FROM { date_value | interval_value } )