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@xxxxxxxxx>
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.
