My Oracle Support Banner

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

Last updated on JUNE 28, 2021

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.1.0.7 [Release 11.1]
Information in this document applies to any platform.

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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
Scope
Details
 Summary of Examples
 1.  Baseline Example
 2.  CG has more columns defined than are present in the predicate
 a. CG is not used by the CBO
 b. CG is used by the CBO
 
3. Choosing a CG when Multiple CGs available
 a. One CG is an exact match to the predicate, does CBO consider others?
 b. Using a CG when it is a subset of the predicates
 c. Using a CG when it is a subset of the predicates and with an additional non-equality operator
 
4. CGs and Histograms
 a. Perfect match CG is not used if CG doesn't have a histogram
 b. Columns with Skewed Data, Which is better Histograms or CGs?
 c. Other examples
 Conclusion
References

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