隐式转换时，不会走索引。为什么 varchar2--> number,有隐式转换； number--->varchar2，怎么就没有隐式转换呢?
SQL> drop table test purge;
SQL> create table test(id varchar2(100),name varchar2(100));
SQL> INSERT INTO test
2(id, NAME)
3SELECT to_char(rownum) AS id, 'name_' || rownum AS NAME
4 FROM dual
5CONNECT BY rownumcreate index ind_test on test (id);
SQL> ALTER system flush shared_pool;
SQL> ALTER system flush BUFFER_CACHE;
SQL> set autotrace traceonly;
SQL> set timing on;
SQL> select * from test where id=1; --这个语句不走索引，能理解，因为有隐式转换。
已用时间:00: 00: 00.17
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT|| 4 | 416 | 104 (2)| 00:00:02 |
|*1 |TABLE ACCESS FULL| TEST | 4 | 416 | 104 (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("ID")=1)
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_9f0t61rapfyzd97bbe3d0" used for this statement
SQL> set autotrace off;
SQL> set timing off;
SQL> drop table test purge;
SQL> create table test(id number,name varchar2(100));
SQL> INSERT INTO test
2(id, NAME)
3SELECT rownum AS id, 'name_' || rownum AS NAME
4 FROM dual
5CONNECT BY rownumcreate index ind_test on test (id);
SQL>
SQL> ALTER system flush shared_pool;
SQL> ALTER system flush BUFFER_CACHE;
SQL> set autotrace traceonly;
SQL> set timing on;
SQL> select * from test where id='1';--不明白这个语句，怎么会走索引呢？id类型是number，我给的是字符，应该有转换，不走索引才对啊？
已用时间:00: 00: 00.06
执行计划
----------------------------------------------------------
Plan hash value: 3856466897
----------------------------------------------------------------------------------------
| Id| Operation
| Name | Rows| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|
| 1 |65 | 2 (0)| 00:00:01 |
| 1 |TABLE ACCESS BY INDEX ROWID| TEST | 1 |65 | 2 (0)| 00:00:01 |
|*2 | INDEX RANGE SCAN
| IND_TEST | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_bdcx4n8hmwgbdb8b4d7d8" used for this statement
SQL> set autotrace off;
SQL> set timing off;
SQL> spool off;
|
