indeed I did, but she fixed it correctly, I think, by putting the or is null on
the col_to_date.
And I offer a similar pleading to yours. We are in fact retired dinosaurs.
Everyone should understand to take the relevant amount of salt with your
brilliances and my meanderings.
I don’t know how to trick the optimizer into counting those nulls in the stats
other than the kludge I suggested of actually updating the nulls into
“tomorrow” (which is no trick at all, at least not on the optimizer.)
I look forward to seeing if you do.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Jonathan Lewis
Sent: Tuesday, October 27, 2020 1:49 PM
To: ORACLE-L
Subject: 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@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.
