Introduced 11gR1 and based on virtual columns. Allows better selectivity.
Condition has the be equality or IN-lists.
We should use them when we have issues by an order of magnitude on the cardinality.
- Column Group: Have to be from same table. Max 32
- Expression: CBO does not know how an applied function impacts the cardinality of a column so it uses default of 1%!!!
They can be seen in DBA_TAB_COLS by checking the hidden columns. Or in data dictionary by checking *_STAT_EXTENSIONS. They have system generated names. The 12c prefix is different of the 11g prefix.
Question: how to fond the correlations?
- 11gR1 manually
- 11gR2 manually / assisted
- 12gR1 manually / assisted / automatic