1.在SELECT查询的时候 如果左外连接,如果查询结果为全部
(例如SELECT * FROM TABLE A LEFT JOIN TABLE B ON A.INDEX=B.INDEX)则不会使用到索引;
Plan Hash Value : 424321251
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14454 | 4553010 | 78 | 00:00:01 |
| * 1 | HASH JOIN OUTER | | 14454 | 4553010 | 78 | 00:00:01 |
| 2 | TABLE ACCESS FULL | TABLE A | 877 | 178031 | 9 | 00:00:01 |
| 3 | TABLE ACCESS FULL | TABLE B | 13878 | 1554336 | 69 | 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("A"."DPET_ID"="B"."DPET_ID"(+))
如果查询结果为个别列
(例如 SELECT A.COU FROM TABLE A LEFT JOIN TABLE B ON A.INDEX=B.INDEX)
如果ON的条件为索引,则会使用索引
Plan Hash Value : 3201551769
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14454 | 361350 | 72 | 00:00:01 |
| * 1 | HASH JOIN OUTER | | 14454 | 361350 | 72 | 00:00:01 |
| 2 | INDEX FAST FULL SCAN | INDEX A#1 | 877 | 6139 | 3 | 00:00:01 |
| 3 | TABLE ACCESS FULL | TABLE B | 13878 | 249804 | 69 | 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("A"."DPET_ID"="B"."DPET_ID"(+))