Re: Optimizer estimated cardinality very low

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