Re: improve cardinality estimate

partition_date, order_procesing_date and business_procesing_date are columns with histograms (hybrid) of table orders.

În joi, 18 mar. 2021 la 14:39, pier paolo Bruno <pbrunoster_at_gmail.com> a scris:

> 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