improve cardinality estimate
Date: Thu, 18 Mar 2021 14:25:12 +0200
Message-ID: <CA+riqSWmwE+wcKWoBfTqL1hSU_G=_E65pY=6Quq0ygy-KnLy_Q_at_mail.gmail.com>
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(*)
orders
partition_date = sysdate-1 and
the count itself is = 42K
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?
from
where
order_procesing_date > = business_procesing_date
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
Thank you.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 18 2021 - 13:25:12 CET