Re: improve cardinality estimate

Are there histograms under orders. order_procesing_date ?

Il giorno gio 18 mar 2021 alle ore 13:26 Laurentiu Oprea < laurentiu.oprea06_at_gmail.com> ha scritto:

> Hello,
>
> I recently saw a query with a wrong execution plan and looks to be caused
> by an overestimate of a particular where clause. Ex:
>
> select
> count(*)
> from
> orders
> where
> partition_date = sysdate-1 and
> order_procesing_date > = business_procesing_date
>
> the count itself is = 42K
> the count itself without second condition ~ 24M
> the cardinality estimate ~ 24M
> the cardinality estimate if I add parallel hint: 1M (this is an
> interesting one and looks to be related with the fact that parallel queries
> get DS level 8)
> the cardinality estimate if I rewrite the condition :
> order_procesing_date - business_procesing_date >= interval '0' minute
> :1.4M
>
> I haven't tried yet but I suspect that if I create extended stats on
> expression ( order_procesing_date - business_procesing_date) can get me
> better results
>
> The question is what is the logic behind beter cardinality estimates if I
> rewrite the where clause?
> What will be the best approach for this query?
>
> Thank you.
>

--
http://www.freelists.org/webpage/oracle-l