Query performance using TO_CHAR in WHERE clause

Its not TO_CHAR() itself, its the fact that using _any_ function to convert a column negates the use of the index unless you also create a function based index with that function on the column. In general, if a conversion needs to happen in a predicate, then use conversion functions on the predicate input values, not the columns.

Yes, it has to TO_CHAR each value of EVENTDATE in your table to evaluate the predicate.  Now that may not be too bad if you had a function-based index on EVENTDATE column.  However, I'd recommend writing it like below:
AND T.EVENTDATE between to_date('2009-08-20', 'YYYY-MM-DD') and  to_date(''2009-08-20','YYYY-MM-DD')

That way, if you have a index on EVENTDATE the optimizer can use that index - most probably use an index range scan.

Good point by Andy. Another point about lexical range criteria, they aren't always correct, depending on the string format. Consider this:

select * from dual where '02-01-1957' between  '01-01-2009' and '12-31-2009';


The "string" is lexically in the range, due to the alphanumeric values, but as a date, it is not chronologically in between, so as Andy said, treat a date as a date.

You want to avoid any conversion if possible. I would do it like this:
Where
AND
(
  trunc(t.eventdate) > Trunc(To_date('20AUG2009'))  AND  
  trunc(t.eventdate) <  Trunc(To_date('20AUG2009')) + 1  
)

But there are several ways to do this, try a few of them and do an explain plan on each of them to show you if any optimization. Toad has that built in or you can use this below.

select * from plan_table where statement_id = 'YourName'        
delete plan_table where statement_id = 'YourName'        

SELECT LPAD(' ',2*(level-1)) || operation || ' ' || options || ' '
    ||object_name||' '||
    DECODE(id,0,'Cost = '|| position) AS "Query Plan"
    FROM plan_table
    START WITH id = 0
    AND statement_id='YourName'        
    CONNECT BY PRIOR ID = PARENT_ID
    AND statement_id = 'YourName';
       
        EXPLAIN PLAN
            SET STATEMENT_ID = 'YourName'        
            INTO plan_table
            FOR
Place your SQL HERE

>> I would do it like this:
>>  trunc(t.eventdate) > Trunc(To_date('20AUG2009'))  AND  
>>  trunc(t.eventdate) <  Trunc(To_date('20AUG2009')) + 1  


No, you do not want to do that, and is the point of the answers above. trunc(t.evendate) kills any chance of using the index unless you have created a function based index on the column. There is no need to use trunc() on the column anyway when using a range condition, since you are not comparing for exact date/time.

looks like you need to modify a bit:

AND T.EVENTDATE >= TO_DATE('2009-08-20', 'YYYY-MM-DD') and T.EVENTDATE < TO_DATE('2009-08-20', 'YYYY-MM-DD')+1

because you want all records on this day...

and

AND T.EVENTDATE between TO_DATE('2009-08-20', 'YYYY-MM-DD') and TO_DATE('2009-08-20', 'YYYY-MM-DD')

does not give you all the records you want...

AND T.EVENTDATE >= TO_DATE('2009-08-20', 'YYYY-MM-DD') and T.EVENTDATE < TO_DATE('2009-08-20', 'YYYY-MM-DD')+1

                                              

1:

if you want all the records for the given day then use it as given by "HainKurt"

AND T.EVENTDATE >= TO_DATE('2009-08-20', 'YYYY-MM-DD') and T.EVENTDATE < TO_DATE('2009-08-20', 'YYYY-MM-DD')+1
 
or you can even use BETWEEN

AND T.EVENTDATE between TO_DATE('2009-08-20', 'YYYY-MM-DD') and TO_DATE('2009-08-20', 'YYYY-MM-DD')+1

=====================================================================

On a separate note, by looking at the query where clause which you posted while asking the question...

AND to_char(T.EVENTDATE, 'YYYY-MM-DD') between '2009-08-20' and '2009-08-20'    --> why both are 2009-08-20 here..

i think you made a typo otherwise can you state your requirement clearly to make us understand why you need the same date two times there and what is the that your query should return the records for ?

nav_kum_v: the sql @ 25184505

AND T.EVENTDATE between TO_DATE('2009-08-20', 'YYYY-MM-DD') and TO_DATE('2009-08-20', 'YYYY-MM-DD')+1

includes the records from next day, between is INCLUSIVE...