Re: which way is faster?

Comments below.

Anurag

"robotech" <NOSPAMrobotech_at_asia1.com> wrote in message news:aii5uj$lgl$1_at_mango.singnet.com.sg...
> Dear All
>
> Q1) I would like to check if anyone knows which of the below 2 ways will/
> should be faster.
> Pls assume no index & all things being equal.
>
> SELECT *
> FROM table
>
> (1) where trunc(sent_date) between '07-APR-2002' and '13-APR-2002';
>
> (2) where sent_date < '14-APR-2002'
> and sent_date >= '07-APR-2002';
>

If there is an index on sent_date. Option 2 *might* be faster since option 1 will disable the use of the index (due to the trunc function applied to it).
See the difference in explain plans to confirm.

>
> I have tried on a 3million records table and the result is
> (1) takes 2hr 15min
> (2) takes 1hr
>
> I am assuming now that (1) is slow due to trunc.
>
> Q2) So if sent_date have data in 'DD-MON-RRRR HH24:MI:SS'
> is this statement
> where sent_date between '07-APR-2002' and '13-APR-2002'
> the same as (2) above

No. between '07-APR-2002' and '13-APR-2002' means between '07-APR-2002 00:00:00' and '13-APR-2002 00:00:00'. The statement should be:
between to_date('07-APR-2002', 'dd-mon-yyyy hh24:mi:ss')

       and to_date('13-APR-2002 12:59:59', 'dd-mon-yyyy hh24:mi:ss')

or create a funtion index on trunc(create_date) and apply option 1 to it.

Its always good practise to specify date format explicitly using to_date.

>
>
> Pls advise
> Many Thanks
>
>
> newbie
>
>
>
Received on Sun Aug 04 2002 - 10:06:26 CDT