Databases Reference
In-Depth Information
The following is the last query of the recipe:
SELECT CUST_FIRST_NAME, CUST_LAST_NAME
FROM CUSTOMERS WHERE CUST_FIRST_NAME = 'Darby';
In the first case, we have a full table scan, because we cannot retrieve all the data from the
index, so we have to do a
TABLE ACCESS BY ROWID
operation for each row, which satisfies
the predicate. The latter query, instead, can be answered without accessing the table data,
because we have asked only the indexed fields.
In the first query, if we use the
INDEX FAST FULL SCAN
, we have to visit every index block
and, for each row with
CUST_FIRST_NAME
equaling
'Darby'
, we have to do a
TABLE ACCESS
BY ROWID
to retrieve
CUST_YEAR_OF_BIRTH
and
CUST_EMAIL
field values. The optimizer has
decided that this kind of access isn't as fast as a full table scan, hence the choice.
We can use the index even in the first query, using an optimizer hint (which will be discussed
in depth in
Chapter 7
):
SELECT /*+ INDEX(CUSTOMERS IX3_CUSTOMERS) */
CUST_FIRST_NAME, CUST_LAST_NAME,
CUST_YEAR_OF_BIRTH, CUST_EMAIL
FROM CUSTOMERS WHERE CUST_FIRST_NAME = 'Darby';
In the next screenshot, we can see that Oracle knows (from the table statistics) that only
43
rows satisfy the
where
condition. If we use the index, we are going through 55500 potential
row accesses (the first plan in the picture is from the previous query, the second one from the
query shown in the screenshot itself):
