Re: sysdate query

From: Ed Prochak <edprochak_at_gmail.com>
Date: 9 May 2006 11:26:27 -0700
Message-ID: <1147199187.366921.67380@i39g2000cwa.googlegroups.com>

jernigam_at_kochind.com wrote:
> I was tuning a query and found some strange behavior based on using
> sysdate in the selection criteria.
>
> if I use the criteria
> and acctg_date > trunc(sysdate)
> it does a full table scan, but if I use the criteria
> and acctg_date > trunc(sysdate) -0
> it does an index range scan on and index on the acctg_date field.
> Both retrieve the same data.
>
> The second one performs much better at 14 seconds compared to 6
> minutes.
>
> Can anyone tell me why adding the "-0" would change the access path?
>
> This is a 9.2.07 database running on Solaris.

What data type is the acctg_date column? (any chance it is not DATE??)

Or was there a known bug in 9.2's optimizer? (I vaguely remember something like this.) Maybe try a google search in this group. (I'm not sure what phrases to suggest.)

Ed Received on Tue May 09 2006 - 13:26:27 CDT