2
    select count(*) as a 
    from event 
    where eventName='ARTICLE' 
    and to_char(event_date,'DD-MM-YYYY')= '08-04-2016';

I want to compare date using a specific date format but when I run this query it is taking lot of time. Is there any way which I can get same result with minimum time?

APC
  • 144,005
  • 19
  • 170
  • 281
suraha
  • 387
  • 2
  • 4
  • 16
  • 2
    Why are you doing a `to_char` on `event_date` which I'm guessing is a `date` rather than doing a `to_date` on the string? Are you trying to force the time component of `event_date` to be set to midnight? What index(es) are available? Is `event_date` indexed? What indexes are defined on the table? – Justin Cave Apr 13 '16 at 06:27
  • I guess you want the whole day. Try: `select count(*) as a from event where eventName='ARTICLE' and event_date between to_date('08-04-2016','dd-mm-yyyy') and to_date('09-04-2016','dd-mm-yyyy')` – dcieslak Apr 13 '16 at 06:37
  • Please post your table structure (including indexes) and the explain plan of your query – Aleksej Apr 13 '16 at 07:09

1 Answers1

2

Do you have an index on EVENT_DATE? If you don't then you've got a full table scan and that's as fast as it gets without building an index.

If you do have an index your query won't use it, because the to_char() will disable it. Let's assume your table has enough different dates and that they are sufficiently clumped together to make it worth using an index. You can change the query to use an index in a couple of ways.

If EVENT_DATE contains only the date element use this

where event_date = date '2016-04-08'

If EVENT_DATE contains a time element use this

where event_date >= date '2016-04-08'
and event_date < date '2016-04-09'

Note that Oracle stores DATE values in a special way. They are not stored with any particular mask. Formatting is just a display thing. So all we need to do is pass the target date in a meaningful format.

APC
  • 144,005
  • 19
  • 170
  • 281