Oracle在执行自隐式转换时:总是会把字符串转为数字,字符串转为日期。当列进行转换,会跳过索引,降低性能。
- 创建一个表格,给三个列建立索引,进行测试。
create table t1(n1 number, v1 varchar2(10), d1 date);
insert into t1
select
rownum n1
, rownum v1
, sysdate + dbms_random.value(0,365)
from
dual
connect by level <= 1e3;
create index t1_n1_idx on t1(n1);
create index t1_v1_idx on t1(v1);
create index t1_d1_idx on t1(d1);
View Code
- 查看执行计划,v1列因为隐式to_number,所以没有走索引
select count(1) from t1 where v1 = 1
下面的走索引T1_V1_IDX
select count(1) from t1 where v1 = "1"
- 查看执行计划,因为v1列需要转日期,所以不走索引
只要列不转类型,就走索引
总结自:https://www.red-gate.com/simple-talk/sql/oracle/oracle-data-type-implicit-conversion-hierarchy/