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@xxxxxxxx> 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@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Laurentiu Oprea
*Sent:* Tuesday, October 27, 2020 11:31 AM
*To:* ORACLE-L (oracle-l@xxxxxxxxxxxxx)
*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.
