Databases Reference
In-Depth Information
14. The following are the results of the previous two queries:
How it works...
When we execute the queries in the
TEST CASE
for the first time, there aren't indexes in
place to help us, so every query ends in a
FULL TABLE SCAN
operation over the
CUSTOMERS
table. This means that we will scan all the database blocks of the
CUSTOMERS
table to retrieve
the data we need, applying the different filters specified in our queries.
To solve this issue, we create the first index on the
CUSTOMERS
table—
IX1_CUSTOMERS
—
made up by the columns
CUST_LAST_NAME
and
CUST_FIRST_NAME
.
When we execute the
TEST CASE
again, we have the following changes in place to retrieve
the rows queried:
F
We use the index
IX1_CUSTOMERS
to answer the first two queries. If we try to
execute these queries, however, we will see that the first will answer "no rows
selected", because the names in the
CUSTOMERS
table are stored with the first letter
capitalized, so there is a "Wade" customer, but not a "WADE" one.
