Databases Reference
In-Depth Information
Listed next are the statistics displayed by Autotrace for this example:
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
585 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
The
consistent gets
value indicates there were three read operations from memory (
db block gets
plus
consistent gets
equals the total read operations from memory). Since the index blocks were
already in memory, no physical reads were required to return the result set of this query. Additionally,
two rows were processed, which matches the number of records in the
CUST
table with a last name of
ACER.
An example that results in an index fast full scan is demonstrated next. Consider this query:
SQL> select count(last_name) from cust;
Using
SET AUTOTRACE ON
, an execution plan is generated. Here is the corresponding output:
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 102 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| 2 | INDEX FAST FULL SCAN| CUST_IDX1 | 103K| 1108K| 102 (2)| 00:00:02 |
-----------------------------------------------------------------------------------
This output shows that only the index structure was used to determine the count within the table. In
this situation, the optimizer determined that a full scan of the index was more efficient than a full scan of
the table.
Scenario 2: All Information Is Not Contained in the Index
Now consider this situation: suppose you need more information from the
CUST
table. Let's begin with
the previous section's query and additionally return the
FIRST_NAME
column in the query results. Now
you need to access the table itself for that one data element. Here's the new query:
SQL> select last_name, first_name from cust where last_name = 'ACER';
Using
SET AUTOTRACE ON
and executing the prior query results in the following execution plan:
-----------------------------------------------------------------------------------------
