Re: improve cardinality estimation

This is actually a very small table. Total number of rows is 35336. The extreme part is the fact that col_to_date has 35209 null values.

Count of the table using that where condition is 30621. Estimated cardinality is 77.

Creating extended stats on trunc(col_from_date) improves the cardinality estimation to 1765.
But not sure how to tackle "trunc(nvl(col_to_date,sysdate+1))"

Appreciate your feedback
Laurentiu.

În mar., 27 oct. 2020 la 17:40, Jonathan Lewis <jlewisoracle_at_gmail.com> a scris:

>
> 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