Full Table scan is used under the rule-based optimization.
A full table scan is used if:
- No Indexes exist on the table.
- No limiting conditions are placed on the rows(that is, the query asks for all the rows). ex. if the query has no where clause, all the rows will be returned.
- No limiting conditions are placed on the rows corresponding to the leading column of any index on the table. ex. if a three-column concatenated index is created on the City-State-Zip columns, then a query that had a limiting condition on the State column only, would not be able to use the Index, since State is not the leading column of the Index .
- Limiting conditions are laced on the lead column of an Index, but the conditions are used inside expressions. ex. If an Index exists on the City column, then a limiting condition of:
WHERE CITY = 'NEW YORK'
could use the index, But , if the limiting condition was instead:
WHERE UPPER(CITY) = 'NEW YORK'
then the index on the City column would not be used because the City column is inside the UPPER function. If you had concatenated the City column with a text string, the index would not be used. ex. if the limiting condition was:
WHERE CITY 'X' LIKE 'ORLANDO%'
then the index on the City column would not be used. - Limiting conditions placed on the rows correspond to the leading column of an index, but the conditions are either NULL checks or inequalities.
For example, if an index exists on the City column, none of the following will be able to use the index:
WHERE CITY IS NULL
WHERE CITY IS NOT NULL
WHERE CITY != 'ORLANDO' - Limiting conditions placed on the rows correspond to the leading columns of the index, but the conditions use the LIKE operatorand the value starts with '%' or the value is a bind variable. ex. neither of the following will be able to use the index:
WHERE CITY LIKE '%YORK%'
WHERE CITY LIKE : CITY_BIND_VARIABLE
NOTE:- The bind variable may contain trailing '%' or no '%' at all. Regardless, an index will not be used.
If cost-based optimization is used, Oracle will use full table scans for all of the above cases shown for rule-based optimzation. Additionally, the cost-based optimizer may decide to use full table scans if the table has not been analyzed, if the table is small, if the indexed columns are not selective, or if the optimization goal is set to ALL_ROWS.
To avoid unplanned full table scans, make sure the query can use an index.
