Databases Reference
In-Depth Information
F
To solve the issue represented by the possible different cases used in the
CUST_LAST_NAME
field, the third query makes use of the
UPPER
function to
make a case-insensitive search. This query won't use the
IX1_CUSTOMERS
index, because of the function in place.
F
The last three queries of the
TEST CASE
aren't affected by the new index; they will
end in a full table scan operation as before.
To solve the issue of the case-insensitive search, we create a function-based index,
IX2_CUSTOMERS
, which uses the
UPPER
function in the definition of the indexed columns.
The new index affects the results for the third query of the
TEST CASE
, because it uses
the index to retrieve the data, avoiding the full table scan operation needed before.
Even after the
IX2_CUSTOMERS
index creation, the last three queries are answered with
a full table scan operation.
We create the
IX3_CUSTOMERS
index to show how we can create a descending index, that
is, an index which stores the data for one or more fields in descending order. In our index, we
have the
CUST_LAST_NAME
field in canonical order, and the
CUST_YEAR_OF_BIRTH
ield
in descending order. This kind of indexing lets us speed up the sorting operation when we
execute a query with different ordering for the columns.
The query that we execute to test the new index will use it similarly to the other indexes we
have created earlier. We avoid a
SORT
phase because using the index retrieves the rows in
the correct order.
We can also create a function-based descending index.
In our
TEST CASE
, the last three queries didn't use either of the indexes we have created.
This happens because we have an equality predicate on the
CUST_FIRST_NAME
, which is
referenced in our indexes as the second field. In these situations, the indexes aren't used by
the database. The last query, instead, will use the index even if we are filtering the table on
the
CUST_FIRST_NAME
; this test allows us to dispel a myth. Oracle uses the indexes even
if the leading columns are not referenced in the
WHERE
predicate of the query. We can see
that in such a case, the operation will be an
INDEX FAST FULL SCAN
. Conversely, when
we reference the leading column of the index, we will end up in an
INDEX RANGE SCAN
.
There's more...
In this recipe, we have used standard B-tree Indexes. A B-tree Index is a very common data
structure in computer science, used to improve the performance of data access, when we are
interested in retrieving only a small percentage of the overall data in the table.
