First page Back Continue Last page Graphics
Can CBO Make Wrong Choice?
Sometimes CBO chooses full scan when index access performs better
Why?
- Missing or unusable index
- Poorly clustered data
- Stale statistics
- Obsolete stats can mislead CBO
- Conditions prohibit index
- Function on indexed column?
- Skewed data combined with bind variables
Notes:
You may have already experienced a situation where CBO chose a full scan and – because index access was faster – you applied a hint to force CBO to use an index. Why does this happen? Many things can cause this, including:
Missing indexes. Always query ALL_INDEXES to be sure the index you think should be used exists and is VALID.
Clustering factor. Table data that is very “unclustered” (not in the same sort sequence as the index) raises the cost of using the index. We’ll study more about index access paths and the impact that clustering factor has on them later in this lesson.
Stale statistics. Check the ALL_INDEXES.LAST_ANALYZED column to be sure that the statistics are current.
A function or arithmetic operation coded on the indexed column. Refer to the SQL Coding Tips lesson for more discussion of what might prevent index use.
Notes for this slide continue on the next page…