Re: improve cardinality estimation

My apologies if that was already mentioned. (This thread is rather long). sysdate <= sysdate + 1 always holds true. I do not think the optimizer is capable of analysis at that level which goes beyond a straight logical calculus transformation.
You could use some constant "where  sysdate between trunc(col_from_date) and trunc(nvl(col_to_date,to_date('12312999','MMDDYYYY')))".

This approach has his disadvantages too . It can lead to underestimation as it extend the time window unrealistically. It might hold in your case. That's a matter of testing.
Your rewrite is cleaner and I believe that is the preferred solution. Instead of changing the code there is always the option of the sql translation, although I would feel a bit uneasy about it. http://kerryosborne.oracle-guy.com/2013/07/13/sql-translation-framework/

Regards

Lothar

Am 28.10.2020 um 08:39 schrieb Laurentiu Oprea:
> Overall, I start to suspect that the only solution is code re-write .
> But not sure if this is a legitimate question: if there is an
> equivalent code shape should oracle rewrite the query behind the
> scenes with that shape? Or in this situation the "sysdate" scares him
> being non-deterministic, even if the code with the different shape is
> not influenced by non-deterministic character of sysdate?
>
> În mie., 28 oct. 2020 la 07:08, Laurentiu Oprea
> <laurentiu.oprea06_at_gmail.com <mailto:laurentiu.oprea06_at_gmail.com>> a
> scris:
>
> Dynamic sampling 11 was the nightmare until not so long. Due to a
> bug the automatic determined value was 11. It was generating a
> parsing time of over 5 minutes to parallel queries and when
> downgrade was happening due to various reasons I could see even 30
> minutes of parsing (there were even extreme cases of 2-3 hours).
>
> Thanks for suggestion, although indeed solves the cardinality
> issues in my case the side effect is crazy parse time for some
> other more important queries.
>
> În mie., 28 oct. 2020 la 04:12, Mladen Gogala
> <gogala.mladen_at_gmail.com <mailto:gogala.mladen_at_gmail.com>> a scris:
>
> Is this an OLTP instance? If it is not, you may try with
> OPTIMIZER_DYNAMIC_SAMPLING=11. If that is an OLTP instance, using
> dynamic sampling will increase parsing time and lower the
> performance,
> albeit not drastically.
>
> On 10/27/20 11:31 AM, Laurentiu Oprea wrote:
> > Hello,
> >
> > I found a query with a very bad execution plan due to low
> > cardinality evaluation of below where clause
> >
> > where
> > sysdate between trunc(col_from_date) and
> > trunc(nvl(col_to_date,sysdate+1))
> >
> > Is there any way I can improve the estimate (like extended
> stats, etc) ?
> >
> > Thanks for your help.
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l