再来个两个都用index的:
SQL> drop table test1;
Table dropped.
SQL> create table test1 as select * from dba_objects;
Table created.
SQL> create index idxtest1 on test1(object_name);
Index created.
SQL> set autotrace trace exp stat
SQL> select /*+use_hash(a,b)*/ count(*) from test1 a,test2 b
2 where to_char(a.object_id)=b.object_name and b.object_id='123'
3 and a.object_name='456'
4 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 732362093
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 158 | 11 (10)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 158 | | |
|* 2 | HASH JOIN | | 1 | 158 | 11 (10)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 79 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDXTEST1 | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| TEST2 | 8 | 632 | 9 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDXTEST2 | 8 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."OBJECT_NAME"=TO_CHAR("A"."OBJECT_ID"))
4 - access("A"."OBJECT_NAME"='456')
6 - access("B"."OBJECT_ID"=123)