A short story about Oracle CBO and data – part 3 - 11g

I have decided to check that same query on 11g.
And there is an improvement – I gathered a statistics and it is running without any problems.
So upgrade to 11g can be a step in good direction.
The main thing is that in 11g a new multicolumn join selectivity algorithm had been
used. I have to do some more research on that. Right now I want to show only
difference using my query as an example



Trace file from 10.2.0.4

Using multi-column join key sanity check for table TAB8
Revised join sel:6.1576e-04 = 0.0021556 * (1/1624.00) * (1/0.0021556)
Join Card:  126.39 = outer (373.19) * inner (550.00) * sel (6.1576e-04)
Join Card - Rounded: 126 Computed: 126.39

Trace file from 11.1.0.7

ColGroup cardinality sanity check: ndv for  TAB8[H] = 550  TAB7[S] = 1
Join selectivity using 1 ColGroups: 0.002156 (sel1 = 0.000000, sel2 = 0.000000)
Join Card:  442.439495 = = outer (373.189655) * inner (550.000000) * sel (0.002156)
Join Card - Rounded: 442 Computed: 442.44

Please compare line 3 in both of them. Did you realize that computed join cardinality in 11g is almost 3.5 times bigger ?
In 10g a join selectivity was based on join columns selectivity, in 11g ?
I don't know yet.

update 18.05.2009
I shouldn't write anything during a weekend ;)
In 11g there is a new feature called a multicolumn histogram statistics
and now this is a source of cardinality in multicolumn joins.
See example on Pythian blog



ps.
Thanks to Alex GorbatchevI have a new syntax highlighting.

regards,
Marcin