---------------------------------------------------
1 -access("D1"."OBJECT_ID"="D2"."OBJECT_ID")
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
6353 consistent gets
0 physical reads
0 redo size
3388939 bytes sent via SQL*Net toclient
53874 bytes received via SQL*Netfrom client
4852 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
72762 rows processed
这里的物理读为0. 但是还是走的是全表扫描。
--刷新一下buffer，增加索引条件：
SQL> alter system flush buffer_cache;
系统已更改。
SQL> select d1.object_name,d2.object_type fromdave d1,dave2 d2 where d1.object_id=d2.object_id and d1.object_id <100;
已选择98行。
执行计划
----------------------------------------------------------
Plan hash value: 504164237
----------------------------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 |SELECT STATEMENT | | 3600 | 189K| 23 (5)| 00:00:01 |
|* 1 | HASH JOIN | | 3600 | 189K| 23 (5)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DAVE2 | 3600 | 86400 | 11 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_DAVE_OBJECT_ID2 | 648 | | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DAVE | 3626 | 106K| 11 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_DAVE_OBJECT_ID | 653| | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -access("D1"."OBJECT_ID"="D2"."OBJECT_ID")
3 -access("D2"."OBJECT_ID"<100)
5 -access("D1"."OBJECT_ID"<100)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
20 consistent gets
6 physical reads
0 redo size
3317 bytes sent via SQL*Net toclient
590 bytes received via SQL*Netfrom client
8 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
98 rows processed
SQL>
走索引之后，物理读从1558降到6.