RE: improve cardinality estimation
Date: Tue, 27 Oct 2020 12:51:13 -0400
Message-ID: <224401d6ac81$61e514d0$25af3e70$_at_rsiz.com>
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,
Thanks for your help.
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Oct 27 2020 - 17:51:13 CET