Re: improve cardinality estimation

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_at_rsiz.com> 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_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