Re: sysdate query

For the optimizer calculations in 9.2

    sysdate is handled as a known constant     trunc(sysdate) is handled as a known constant     sydate +/- N is treated like an unpeekable bind value     trunc(sysdate) +/- N is treated like an unpeekable bind value

Consequently your first predicate

    > and acctg_date > trunc(sysdate)
has a selectivity based on actual values for sysdate and the column low/high values, the second predicate
> and acctg_date > trunc(sysdate) -0

has a selectivity of 5%.

This changes in 10g (possibly 10.1.0.4, maybe earlier - I don't recall without checking my notes.)

See the book for further details.

    http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html


<jernigam_at_kochind.com> wrote in message 
news:1147183694.353042.133210_at_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.

>