Re: explain a sql statement

> What is going on here? Why does to_date function cause a full table
> scan?

It may well be because you provide a literal in the second case rather than a bind variable. Oracle (allegedly) can use the literal value to improve its choice of access path.

> How should I advice our developper to improve
> the sql, or how could I just create an outline for it to bypass the
> problem?

Use a hint in the statement - you may find the first_rows hint is all you need:

select /*+ first_rows */ (end_time - to_date('2002-01-23 23:59:59'))*60*24

     from test
    where start_time < to_date('2002-01-23 23:59:59')     and end_time > to_date('2002-01-23 23:59:59'); Received on Sat Jan 26 2002 - 04:25:15 CST