Databases Reference
In-Depth Information
Due to the selectivity of the new value used in the predicate (less than 0.2 percent of the
rows in the table will satisfy the predicate), the database switches the execution plan to an
Index Range Scan operation. The index is scanned (only in the range related to "A" values
for the
CUST_VALID
attribute) to find the rows which satisfy the
WHERE
clause; this is a very
efficient way to access a table (when the returned rows are few when compared to the total
number of rows in the table).
The effectiveness of an index depends on the number of rows
selected out of the total number of rows in the table. This is the
selectivity of an index. In an ideal index, there is only one row for
each index value. In the real world, an index with a selectivity of
less than 10 percent is considered suitable enough.
The last query will ask for the same result-set, but changing the way in which the
predicate is expressed. There are only "A" and "I" values in the column
CUST_VALID
,
so the condition expressed as not equal is equivalent to that of the previous query,
expressed with an equal comparison.
Again, there is a change in the execution plan, as shown in the following screenshot:
Why did the database optimizer switch back to a long-running FTS operation, instead of
the previous Index Range Scan? The answer is simple—indexes cannot be used when we
compare values with a not equal operator.
