Home -> Mailing Lists -> Oracle-L -> Re: Optimizer estimated cardinality very low

Re: Optimizer estimated cardinality very low

From: Jonathan Lewis <jlewisoracle_at_gmail.com>
Date: Wed, 7 Oct 2020 09:59:20 +0100
Message-ID: <CAGtsp8ngg2Nfq0kNMrAMRbkTarKfMBx_C0r81w-u-5U9todzRA_at_mail.gmail.com> 








I should have paid more attention to the last section of the email.



Oracle cannot be using the column groups "properly" to estimate the join
selectivity because the column groups don't match at both ends of the join.
However it can use the 2-column column group at one end of the join, which
would then tend to mean the product of the two column selectivities would
be used at the other end, with a sanity check against the total number of
rows in the table/partition/subpartition (depending on the level at which
the query was running).



Is there already a two-column column group (or index) on the detail table
as well as the three-column one that you showed us?
Are the partition-level stats for the column groups on the July
partitions/subpartitions reasonable accurate?



Although I'm quite interested in what the optimizer is doing with this
query, if I were in your position I would make the changes I've suggested
because those are the correct "generic" changes to give the optimizer the
best possible chance of doing the right thing (and then I'd worry about why
it was going wrong if that didn't help).



Regards


Jonathan Lewis


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


Received on Wed Oct 07 2020 - 10:59:20 CEST












Original text of this message