First page Back Continue Last page Graphics
Why Full Table Scan?
No predicates (filters)
- Unless selected data all stored in index
No predicates on indexed columns
CBO decides index access is more expensive:
- Selectivity is poor
- Table size is very small
- Selectivity is good but clustering factor is poor
Notes:
Under what circumstances will CBO choose a full table scan? A query with no predicates or no predicates on indexed columns will often lead to a full table scan. However, if there are no predicates, but an index contains all the selected columns, CBO might choose a index fast full scan instead (more on this later in this lesson).
There are also factors that can increase the cost of index access. These include poor selectivity of indexable predicates (i.e. a large percentage of rows will pass through the filter), small tables that consume only a few blocks and poor clustering factor. Clustering factor is a measure of the sorted relationship between an index and it’s data. i.e. Is the data in the same sorted sequence as the index? We will study more about index access paths and the impact that clustering factor has on them later in this lesson.