Re: hash join cardinality missestimate

Your assumption is correct IF:

It's a single column join with equality
There are no other predicates on the two tables There are no histograms on the two columns There are no nulls for either column
There are no SQL Plan Directives in place for the columns individually or the join

You have histograms and at least one SQL Plan Directive.

Regards
Jonathan Lewis

On Tue, 5 Oct 2021 at 08:43, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> wrote:

> Thanks a lot Jonathan.
>
> My understanding was that for join selectivity the computed sel value was
> 1/greatest(NDVs) where in my case I was expecting to be 1/(greatest(6,8),
> , but looks like computed sel is 2.1058e-04 . Any level of dynamic
> sampling(from 0 to 8) looks like it is not influencing this value.
>
> So my assumption is wrong?
>
> În mar., 5 oct. 2021 la 10:27, Jonathan Lewis <jlewisoracle_at_gmail.com> a
> scris:
>
>> Insufficient information
>>
>> However you have a combination of two important factors in the join
>>
>> a) All the columns explicitly referenced in the fragment of trace file
>> have frequency histograms
>> b) There's an sql_plan_directive in force which is going to adjust any
>> other calculations the optimizer will do.
>>
>> For the impact of a frequency histograms on joins you could look at a
>> series I wrote time ago. Here's a link to part 2, it refers back to part 1
>> and has pingbacks from parts 3, 4, and 5.:
>> https://jonathanlewis.wordpress.com/2018/10/05/join-cardinality-2/
>>
>>
>> For an understanding (though there's a lot of version dependency to
>> worry about) of SQL Plan Directives you could start with Stefan Koehler's
>> article, which includes several links to material by Mauro Pagano and
>> Franck Pachot.
>> https://blogs.sap.com/2015/06/01/oracle-db-optimizer-part-xii-revealing-sql-plan-directive-details-for-existingloaded-cursor-from-cbo-and-sql-dynamic-sampling-services-trace/
>>
>>
>> Regards
>> Jonathan Lewis
>>
>>
>> On Mon, 4 Oct 2021 at 20:14, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com>
>> wrote:
>>
>>> Hello,
>>>
>>> I have a big cardinality misestimate for a HJ operation:
>>> Join cardinality for HJ/SMJ (no post filters): 28073805.777726, outer:
>>> 42.720832, inner: 7801742.651163, sel: 2.1058e-04
>>> Join Card - Rounded: 70185 Computed: 70184.514444
>>>
>>> Can someone help me understand how optimizer came with that sel value?
>>> (attached 10053 extract)
>>>
>>> Thank you.
>>>
>>

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