Databases Reference
In-Depth Information
There's more...
We have seen how using a not equal comparison in the
WHERE
clause of a query, doesn't
allow us to use an index to select the values.
There are other situations which prevent an index from being used:
F
Using a function: A function is often used as a predicate. In such cases, it's better
to express the function on the constant side of the comparison and not on the field
side. For example, we want to know the orders less than 1000 including the shipping
fee of 5.25 percent. Suppose we have the
TOTAL
column of the
ORDERS
table in
which the value is stored without the shipping fee and this column is indexed, if we
query for
(TOTAL
*
(1
+
5.25
/
100.00))
<
1000.00
, it will not use the index.
If we express the same condition as
TOTAL
<
1000.00
/
(1+5.25
/
100.00)
, the index on the
TOTAL
field will be used—if it's convenient to use
it instead of a Full Table Scan.
F
Searching for NULL values: NULL values are not stored in indexes, so when we
query for the records with a NULL value in a field X, even if the X column is indexed,
the index will not be used.
The index will be used, instead, if we query for NOT NULL elements, but only
if the resulting operation (accessing the index plus accessing the table) is
less expensive than an FTS. In this case, a Fast Full Scan operation will be
performed on the index, that is, a complete scan of the entire index.
See also
F
See the
Indexing the correct way
recipe in
Chapter 3
,
Optimizing Storage Structures
for more info about using indexes
Exploring index skip-scan and index
range-scan
In this recipe, we will see how to use composite indexes and also the difference between
index skip-scan and index range-scan operations.
Getting ready
For this recipe, we will use a copy of the
CUSTOMERS
table in the
SH
schema and SQL*Plus
to execute our tests.
