Re: 11g query taking 24 seconds, same query was instant in 9i.

thanks...

stat's are fresh on 11g (I'll have to check the 9i, since its an old test instance) - just reran the query again, both return 30 rows, and data is the same. 9i this time took .98 and 11g took 10.73. 9i sorts by date field by default whereas 11g does not for some reason. I'll try altering the date as per your suggestion after I put some other fires out..

>
> What I notice is a different way of handling the implicit date
> conversions when dates are compared to strings like
>
> WHERE (sample_date >= '01-apr-09') and (TRUNC(sample_date) <='30-apr-09')
>
> which in 9i are treated as
>
>
> TRUNC("SYS_ALIAS_2"."SAMPLE_DATE")<='30-apr-09')
>
> and in 11g as
>
> TRUNC(INTERNAL_FUNCTION("A"."SAMPLE_DATE"))<='30-apr-09').
>
> You might check if this causes an index to be ignored.
>
> Try
> WHERE (sample_date >= to_date('01-apr-09', 'DD-MON-YY')) and
> (TRUNC(sample_date) <=to_date('30-apr-09','DD-MON-YY'))
>
>
> And: a different estimate of rows returned by
>
> > | 5 | VIEW | VW_CEM_CORR | *10* | 370 | 87 | (*9i*)
>
>
> > | 5 | VIEW | VW_CEM_CORR | *2305* | 29965 | 2126 (4)| 00:00:26 |
> (*11g*)
>
> I wonder if your statistics are correct, or if you're using the same data.
>
> Shakespeare
Received on Wed May 26 2010 - 12:35:07 CDT