Re: Question on 8.1.7

"Gokul" <gokulkumar.gopal_at_gmail.com> wrote in message news:1189354836.746350.308770_at_d55g2000hsg.googlegroups.com...
> Hello,
>
> We hit a strange problem on version 8.1.7.
>
> select col1,col2,col3 from tab1,view1,tab2.. where col5 >= '08-Sep-07'
>
> select col1,col2,col3 from tab1,view1,tab2.. where col5 >=
> to_date(to_char(sysdate-1,'YYYYMMDD'),'YYYYMMDD')
>
> These two queries behave differently. The first one takes less than 2
> secs and the second one takes 15 minutes.
>
> I dont have more details. Just trying my luck here to check if someone
> has faced such problem in the past.
>
> Any ideas ?
>
> PS: '08-Sep-07' and (sysdate - 1) are same.
>
> Rgds,
> Gokul
>

Most likely cause:

'08-Sep-08' is a known constant, which allows an accurate calculation of selectivity. sysdate - 1 is an unknown value which results in an assumed selectivity of 5% in that predicate.

The plan has probably changed from an index range scan to a tablescan. (See Cost Based Oracle - Fundamentals).

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

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

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