Re: Optimizer estimated cardinality very low

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_at_gmail.com> 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_at_gmail.com>
> 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
>
>

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