Databases Reference
In-Depth Information
Scenario 1: All Data Lies in the Index Blocks
This section will describe two scenarios. In each, all data required for query execution, including data
returned to the user as well as data evaluated in the
WHERE
clause, is present in the index.
•
Index range scan
: This occurs when the optimizer determines it is efficient to use
the index structure to retrieve multiple rows required by the query. Index range
scans are used extensively in a wide variety of situations.
Index fast full scan
: This occurs when the optimizer determines that most of the
rows in the table will need to be retrieved. However, all of the information
required is stored in the index. Since the index structure is usually smaller than the
table structure, the optimizer determines that a full scan of the index is more
efficient (than a full scan of the table). This scenario is common for queries that
count values.
First, the index range scan is demonstrated. In this situation, you run the following query:
•
SQL> select last_name from cust where last_name='ACER';
Before reading on, look at Figure 2-1 and try to answer this question: “What are the minimal
number of blocks Oracle will need to read to return the data for this query?” In other words, what is the
most efficient way to access the physical blocks in order to satisfy the results of this query? The optimizer
could choose to read through every block in the table structure. However, that would result in a great
deal of I/O and thus is not the most optimal way to retrieve the data.
For this example, the most efficient way to retrieve the data is to use the index structure. To return
the rows that contain the value of ACER in the
LAST_NAME
column, Oracle will need to read three index
blocks: block 20, block 30, and block 39. You can verify that this is occurring by using Oracle's Autotrace
utility.
SQL> set autotrace on;
SQL> select last_name from cust where last_name='ACER';
Here is a partial snippet of the output:
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| CUST_IDX1 | 1 | 11 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
This output shows that Oracle needed to use only the
CUST_IDX1
index to retrieve the data to satisfy
the result set of the query.
The table data blocks were not accessed
; only the index blocks were required.
This is a particularly efficient indexing strategy for the given query.
Tip
When an index contains all column values required by the query, this is referred to as a covering index.
