I do have a demo as part of my optimizer related workshops that shows the restriction / limitation of DBMS_STATS not supporting extended statistics on virtual columns / group of expressions, so for example the combination of both expressions and column groups, like ((TRUNC(COL1)), (TRUNC(COL2))).
Surprisingly, when following a certain sequence of operation, this starts working (to some degree) from 12.2.0.1 on.
Introduction
I've already outlined in one of my previous posts that getting a reasonable cardinality estimate for multi-column joins can be tricky, in particular when dealing with correlated column values in the join columns.
Since Oracle 10g several "Multi-Column Join Cardinality" sanity checks have been introduced that prevent a multi-column join from producing too low join cardinalities - this is controlled via the "_optimizer_join_sel_sanity_check" internal parameter that defaults to true from 10g on.
Recent comments
2 years 51 weeks ago
3 years 11 weeks ago
3 years 15 weeks ago
3 years 16 weeks ago
3 years 21 weeks ago
3 years 42 weeks ago
4 years 10 weeks ago
4 years 40 weeks ago
5 years 24 weeks ago
5 years 25 weeks ago