Re: improve cardinality estimation

Mark,
I think you've missed the detail that there's a col_FROM_date and a col_TO_date in the original expression,
and I think Laurentiu has produced an expression that isn't logically equivalent to the origins by following your example.

(But I may be wrong, and will plead too much time wearing headphones today as mitigating circumstances).

Regards
Jonathan Lewis

On Tue, 27 Oct 2020 at 16:52, Mark W. Farnham <mwf_at_rsiz.com> wrote:

> do you mean like:
>
>
>
> where
>
> col_from_date is null
>
> or ( trunc(col_from_date) between sysdate and sysdate+1)
>
>
>
> it **should** be able to get the number of nulls and a decent number in
> the range estimate from the stats.
>
>
>
> I would also look into your exact query return desires to see if you can
> lose the trunc on col_from_date with an appropriate formulation of sysdate
> boundaries…
>
>
>
> For a given call, sysdate is a constant, and the optimizer tends to do
> better with functions and calculations on the constant rather than the
> value in the index or table.
>
>
>
> in general I find it less confusing for human readers to write:
>
>
>
> <column_value> is between x and y
>
>
>
> rather than x is between <column_value> and
> some_function_on(<column_value>) that might return y.
>
>
>
> even
>
>
>
> trunc(nvl(col_to_date,sysdate+1)) is between sysdate and sysdate+1
>
>
>
> is more understandable, although that obscures that you want to include
> all null values a bit. Essentially that is the difference between writing
> “I want all the nulls and all the actual dates from now through the next
> day” in place of “I’m going to pretend all the nulls are now plus a day,
> and I want all the values from now through now plus a day.”
>
>
>
> good luck.
>
>
>
> I hope I read all that right and wrote all the right. I need more coffee
> and I’m old, rusty, and cranky.
>
>
>
> mwf
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Laurentiu Oprea
> *Sent:* Tuesday, October 27, 2020 11:31 AM
> *To:* ORACLE-L (oracle-l_at_freelists.org)
> *Subject:* improve cardinality estimation
>
>
>
> 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