Oracle Extended Statistics

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.

Types:

  • 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

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: