MultiColumn/Column Group Statistics - Additional Examples (Doc ID 872406.1)

Last updated on JUNE 28, 2021

Applies to:

Purpose

This note lists additional examples to those shown in <note 452883.1> of how column groups are used by the optimizer in different cases.  This information may help you when you are trying to determine how the cost based optimizer (CBO) estimated cardinality when CGs were available.

Scope

This note will be of interest mostly to application developers and DBAs.

You would typically use column groups to improve the optimizer's cardinality estimates when you see the actual versus estimated cardinality for a plan step differs by a significant amount (50% or more) and the predicate associated with that plan step includes more than one column from the same table (e.g., "WHERE t1.col1 = 5 AND t1.col2 = 10").

A SQL profile can sometimes achieve a similar improvement to the estimated cardinality since the profile will use a cardinality scaling factor that was empirically determined.  However, in cases where the column group has skewed values, the use of column groups with histograms may be more accurate than a SQL profile.  A SQL Profile is also limited to improving the plan for a SQL that matches exactly to the SQL the profile was created, whereas the CBO may use column groups for SQL with similar predicates (or even dynamic SQL in a data warehousing environment). 

Consider a column group as yet another tool to improve cardinality estimates and hence, execution plans.

Details

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.