sysdate query

From: <jernigam_at_kochind.com>
Date: 9 May 2006 07:08:14 -0700
Message-ID: <1147183694.353042.133210@y43g2000cwc.googlegroups.com>

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. Received on Tue May 09 2006 - 09:08:14 CDT