Database Reference
In-Depth Information
That is a pretty common plan: go to the
EMP
table by primary key; get the row; then using that
EMPNO
, go to the
address table; and using the index, pick up the child records. We did 11 I/Os to retrieve this data. Now run the same
query, but use the IOT for the addresses:
EODA@ORA12CR1> select *
2 from emp, iot_addresses
3 where emp.empno = iot_addresses.empno
4 and emp.empno = 42;
Execution Plan
----------------------------------------------------------
Plan hash value: 252066017
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 292 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 4 | 292 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 27 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | SYS_IOT_TOP_182459 | 4 | 184 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMP"."EMPNO"=42)
4 - access("IOT_ADDRESSES"."EMPNO"=42)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1361 bytes sent via SQL*Net to client
543 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
We did four fewer I/Os (the four should have been guessable); we skipped four
TABLE ACCESS (BY INDEX ROWID
BATCHED)
steps. The more child records we have, the more I/Os we would anticipate skipping.
So, what is four I/Os? Well, in this case it was over one-third of the I/O performed for the query, and if we execute
this query repeatedly, it would add up. Each I/O and each consistent get requires an access to the buffer cache, and
while it is true that reading data out of the buffer cache is faster than disk, it is also true that the buffer cache gets are
not free and not totally cheap
. Each will require many latches of the buffer cache, and latches are serialization devices
that will inhibit our ability to scale. We can measure both the I/O reduction as well as latching reduction by running a
PL/SQL block such as this:
EODA@ORA12CR1> begin
2 for x in ( select empno from emp )
3 loop
4 for y in ( select emp.ename, a.street, a.city, a.state, a.zip
