Databases Reference
In-Depth Information
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| CUST | 1 | 39 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | CUST_IDX1 | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
This output indicates that the
CUST_IDX1
index was accessed via an
INDEX RANGE SCAN
. The
INDEX
RANGE SCAN
identifies the index blocks required to satisfy the results of this query. Additionally, the table
is read by
TABLE ACCESS BY INDEX ROWID
. The access to the table by the index's
ROWID
means that Oracle
uses the
ROWID
(stored in the index) to locate the corresponding rows contained within the table blocks.
In Figure 2-1, this is indicated by the dotted lines that map the
ROWID
to the appropriate table blocks that
contain the value of ACER in the
LAST_NAME
column.
Again, looking at Figure 2-1, how many table and index blocks need to be read in this scenario? The
index requires that blocks 20, 30, and 39 must be read. Since
FIRST_NAME
is not included in the index,
Oracle must read the table blocks to retrieve these values. Oracle must read block 39 twice because there
are two corresponding rows in the table. Oracle knows the
ROWID
of the table blocks and directly reads
blocks 11 and 2500 to retrieve that data.
That makes a total of six blocks. With that number in mind, take a look at the statistics generated by
Autotrace.
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
681 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
Notice that these statistics indicate that only five blocks were read (
consistent gets
), yet six were
predicted. This is because some block reads are not accounted for in the Autotrace-generated statistics.
Oracle will pin some blocks and re-read them. In this scenario, block 39 is read once, pinned, and then
re-read for a second time (because there are two
ROWID
s associated with the value of ACER). The count of
re-reads of blocks is collected in the
buffer is pinned count
statistic (which is not displayed in the
Autotrace statistics).
Regardless, the point here is that when the index is accessed there is a back-and-forth read process
between index blocks and data blocks. The number of blocks read when an index is used to provide
ROWID
s for a table will be at least double the number of rows returned (because the index block with the
ROWID
is read and then the corresponding table block is read). And in many scenarios, the
consistent
gets
statistic doesn't accurately reflect the actual number of buffer reads.
