Daniel,
Thanks for the response.
My hypothesis (which may only be correct for some versions of Oracle) is
the with the >= and < setup the optimizer will recognize that it must use
partition-level stats.
Then, with the matching column groups, it should use the column group stats
to do the arithmetic.
At present I think it might be using table-level stats, and comparing the
table-level column group of two-columns on one table with the product of
the two separate columns on the other table. (I have to admit I don't know
why this would get you a reasonable estimate in the June partitions/
Regards
Jonathan Lewis
<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
Virus-free.
www.avg.com
<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
On Wed, 7 Oct 2020 at 17:26, Daniel Coello <coello.daniel@xxxxxxxxx> wrote:
Thank you Jonathan and Lothar.
I have verified recommendation* a) *since values are date only. For this
scenario it didn't change the estimation (I'm making a note about the
"strictly less than" approach as it will be beneficial for standard
practice with this type of data).
For suggestion *b *I am creating the recommended column group to match
both sides. There are no column groups or indexes in the detail table and
statistics at the partition/subpartition level are reasonably accurate for
the July month.
I have checked DBA_PART_COL_STATISTICS and DBA_TAB_COL_STATISTICS for
low_value and high value in both joining columns and they match the values
for corresponding columns in the join.
Thanks again for the feedback, I will post results once the column group
with the 2 columns is created and stats collected.
On Wed, Oct 7, 2020 at 5:00 AM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
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
--
Daniel Coello Villacis
