Re: improve cardinality estimation

Have you used a very high value or very low value to represent some sort of "missing" or "not yet" dates in these columns. Since you have a range-based predicate Oracle will be comparing the ranges you're trying to request against the known low and high values. Check the min() and max() dates from those two columns for extreme values. If you have extreme values you may need to create histograms on the two columns so that Oracle gets a better idea that there's a small volume of date in the far past or far future while the bulk of it is recent or near future.

Regards
Jonathan Lewis

On Tue, 27 Oct 2020 at 15:32, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> 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.
>

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