Thanks for the answer.
Long story short at this point I need to live with this crappy code.
I found that this low cardinality determines the optimizer to choose for
one particular query a NLJ (duration 1 hour) instead of HJ (duration 1
minute).
Rewriting the where clause like:
where (sysdate between trunc(col_from_date) and trunc(col_to_date) ) or
col_to_date is null
gives perfect cardinality
The question will be: is there a smarter solution to give oracle enough
statistical information so that he can figure out a good cardinality with
the current where clause?
În mar., 27 oct. 2020 la 18:51, Mark W. Farnham <mwf@xxxxxxxx> a scris:
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.
