| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Which is fastest? To_Date.. or Trunc...
A copy of this was sent to "R. Nightingale" <nighr_at_hotmail.com>
(if that email address didn't require changing)
On Sun, 1 Aug 1999 19:24:55 -0500, you wrote:
>Trunc simply sets the date to midnight by removing the fraction. I guess
>you could accomplish the same thing by using a To_Char(datefield,
>'YYYYMMDD') or something like that. I would imagine the Trunc approach
>would be quicker. The date datatype is already a 7-byte floating point
>number. You're welcome to post some benchmarks!
>--rn.
>
that is mostly correct. the part about a floating point number is wrong.
trunc of date zeroes out (logically zeroes out as shown below, zero-ing out the date results in 3 binary 1's being stored) the day, hour, minute, and second bytes of a date (last 3 bytes). for example:
SQL> create table t ( x date, y date ); Table created.
SQL> INSERT INTO T VALUE values ( sysdate, trunc(sysdate) ); 1 row created.
SQL> SELECT DUMP(X) x, DUMP(y) y from t;
X Y ----------------------------------- -----------------------------------Typ=12 Len=7: 119,199,8,3,11,11,47 Typ=12 Len=7: 119,199,8,3,1,1,1
A date in binary format contains seven bytes, as shown below
BYTE 1 2 3 4 5 6 7 Meaning Century Year Month Day Hour Minute Second Example (for 30–NOV–1992,3:17 PM) 119 192 11 30 16 18 1
The century and year bytes are in an excess–100 notation. Dates Before Common Era (BCE) are less than 100. The era begins on 01–JAN–4712 BCE, which is Julian day 1. For this date, the century byte is 53, and the year byte is 88. The hour, minute, and second bytes are in excess–1 notation. The hour byte ranges from 1 to 24, the minute and second bytes from 1 to 60. If no time was specified when the date was created, the time defaults to midnight (1, 1, 1).
the trunc format would almost certainly be faster, not only because its fairly easy to perform (set the last 3 bytes to 1 rather then convert these 7 bytes into a nicely formatted string) but because it does not require any further conversions. If I code:
if (date_field = trunc(another_date_field) ) then
we are comparing a DATE to a DATE (trunc returns a DATE). On the other hand, if I:
if ( date_field = to_char(another_date_field,'dd-mon-yyyy') ) then
i introduced an implicit conversion into the code -- either date_field will be converted into a string or a to_date would be wrapped around the to_char() to make its result back into date.
This block shows that it is doing an extra implicit to_date on the the result of the to_char:
SQL> declare
2 date_field date := trunc(sysdate); 3 another_date_field date := sysdate; 4 begin 5 if ( date_field = to_char(another_date_field,'dd-mon-yyyy') ) 6 then 7 dbms_output.put_line( 'Are Equal' ); 8 else 9 dbms_output.put_line( 'Are not Equal' ); 10 end if; 11 if ( date_field = '03-aug-x999' ) 12 then 13 dbms_output.put_line( 'Are Equal' ); 14 else 15 dbms_output.put_line( 'Are not Equal' ); 16 end if;
since the first one succeeded -- we can guess that it used an implicit to_date. Since the second one failed with a conversion error -- we can be assured it was doing the implicit conversion.
The trunc will not do that.
turning on sql_trace and running tkprof, we can see the difference is really quite large:
declare
date_field date := trunc(sysdate);
another_date_field date := sysdate;
begin
for i in 1 .. 10000 loop
if ( date_field = to_char(another_date_field,'dd-mon-yyyy') )
then
null;
end if;
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0 Execute 1 2.58 2.86 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 2.59 2.87 0 0 0 1
declare
date_field date := trunc(sysdate);
another_date_field date := sysdate;
begin
for i in 1 .. 10000 loop
if ( date_field = trunc(another_date_field) )
then
null;
end if;
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.15 0.19 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.15 0.19 0 0 0 1
Similar differences in speed can be observed in SQL as well:
select count(*) from all_objects
where created = to_char(created,'dd-mon-yyyy')
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 3.70 4.63 21 33721 4 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 3.70 4.63 21 33721 4 1 ********************************************************************************
select count(*) from all_objects
where created = trunc(created)
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.04 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 1.55 1.74 10 33721 4 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 1.59 1.78 10 33721 4 1
>Magnus <magnus.johansson_at_araby-dalbo.comREMOVETHIS> wrote in message
>news:37A2105D.C2106A39_at_eudoramail.comREMOVETHIS...
>> Hi,
>>
>> I have seen a couple of questions on comparing dates. Some recommend
>> To_Date and some recommend Trunc to get rid of the "time-part" of the
>> dates in the comparision. Does anyone know which is fastest when the
>> query runs?
>>
>> Thanks!
>>
>> /Magnus
>>
>
--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Aug 03 1999 - 09:32:47 CDT
 |
 |