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@xxxxxxxxx> a
scris:
Are there histograms under orders. order_procesing_date ?
Il giorno gio 18 mar 2021 alle ore 13:26 Laurentiu Oprea <
laurentiu.oprea06@xxxxxxxxx> 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.
