Home » SQL & PL/SQL » SQL & PL/SQL » Between Vs less that & greater than for Date columns (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
Between Vs less that & greater than for Date columns [message #590246] Tue, 16 July 2013 04:47 Go to next message
saipradyumn
Messages: 188
Registered: October 2011
Location: Hyderabad
Senior Member

Hi Michel,

Is there any difference from the performance prospective for the following two queries ?


select *
  from scott.emp se
 where trunc(se.hiredate) >= to_date('01-jan-1980')
   and trunc(se.hiredate) <= to_date('30-jun-1981')
 order by se.hiredate


select *
         from scott.emp se
        where trunc(se.hiredate) between to_date('01-jan-1980') and
              to_date('30-jun-1981')
       order by se.hiredate


As EMP table contains very less data here I didn't find any difference in execute plan of each query .
What if the table contains very huge amount of data ?
is this applicable to only DATE columns ?


Thanks in advance
saipradyumn
Re: Between Vs less that & greater than for Date columns [message #590248 is a reply to message #590246] Tue, 16 July 2013 05:08 Go to previous messageGo to next message
Michel Cadot
Messages: 60001
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no difference but:
SQL> select to_date('01-jan-1980') from dual;
select to_date('01-jan-1980') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Regards
Michel
Re: Between Vs less that & greater than for Date columns [message #590250 is a reply to message #590248] Tue, 16 July 2013 05:15 Go to previous messageGo to next message
cookiemonster
Messages: 11285
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you're worried about performance you should release that trunc stops oracle using any indexes on the date column.
So this:
select *
  from scott.emp se
 where trunc(se.hiredate) >= to_date('01-jan-1980')
   and trunc(se.hiredate) <= to_date('30-jun-1981')
 order by se.hiredate

Should be:
select *
  from scott.emp se
 where se.hiredate >= to_date('01-jan-1980', 'dd-mon-yyyy')
   and se.hiredate < to_date('30-jun-1981', 'dd-mon-yyyy') + 1
 order by se.hiredate


You can do a similar thing with the between.
Re: Between Vs less that & greater than for Date columns [message #590252 is a reply to message #590246] Tue, 16 July 2013 05:27 Go to previous messageGo to next message
dariyoosh
Messages: 536
Registered: March 2009
Location: Iran / France
Senior Member
Also
. . . where trunc(se.hiredate) >= to_date('01-jan-1980') . . .

May be written by simply using a DATE literal instead of using to_date (because apparently you don't need the time portion)
. . . WHERE trunc(se.hiredate) >= DATE '1980-01-01'


Regards,
Dariyoosh
Re: Between Vs less that & greater than for Date columns [message #590255 is a reply to message #590250] Tue, 16 July 2013 06:39 Go to previous message
pablolee
Messages: 2658
Registered: May 2007
Location: Scotland
Senior Member
cookiemonster wrote on Tue, 16 July 2013 11:15
If you're worried about performance you should release that trunc stops oracle using any indexes on the date column.

<nitpick> Unless the Index truncates the date </nitpick> ... I'll get my coat.
Previous Topic: Trigger for INSERT, UPDATE OR DELETE
Next Topic: TWR--6502: ORA-06502: PL/SQL: numeric or value error: character string buffer
Goto Forum:
  


Current Time: Sat Dec 20 09:54:28 CST 2014

Total time taken to generate the page: 0.16995 seconds