正确高效使用数据库不可不知的索引失效问题

where语句中索引独立出现，索引才会起作用，不要放在表达式中(如: 转换函数一般放在值那边，不要放在列那边)，或发生不合适的隐式转换

 

1.表达式例子

表employees的last_name列上有个索引
下面的语句使用索引
SQL> set autotrace trace exp
SQL> SELECT last_name, job_id, department_id
  2  FROM   employees
  3  WHERE  last_name = 'Whalen';

但是下面的语句不使用索引
SQL> set autotrace trace exp
SQL> SELECT last_name, job_id, department_id
  2  FROM   employees
  3  WHERE  lower(last_name) = 'whalen';

 

2.隐式转换

建立演示表test，在test的employee_id列建有索引
SQL> spool off
SQL> create table test as select * from employees;
表已创建。
SQL> create index i_test_id on test(employee_id);
索引已创建。
SQL> set autotrace trace exp
SQL> select * from test where employee_id='24000';

将列employees的类型由原来的整型修改为字符型，目的是然列employees发生隐式转换
SQL> truncate table test;
表被截断。
SQL> alter table test modify(employee_id varchar2(20));
表已更改。
SQL> insert into test select * from employees;
已创建107行。
SQL> commit;
提交完成。
SQL> set autotrace trace exp
SQL> select * from test where employee_id=100;

在上图中发生了一个隐式转换TO_NUMBER("EMPLOYEE_ID")
解决方法：转换函数一般放在值那边，不要放在列那边，因为在列那边会隐士转换，或其他原因，造成索引失效
SQL> select * from test where employee_id=to_char(100);

 

表employee列salary是NUMBER类型
SQL> select  * from  employee where  salary  like ‘13___’;
系统做了隐士准换 where  to_char(salary) like ‘13__’,则索引失效

 

3.索引实际应用之编码设计

根据需求来作编码，例如按一般的逻辑地区编码为省、市、县
但是系统中实际上大量查询是 like ‘%某一个市%‘，这样索引就失效
所以合适的编码是市、省、县 这样 like ‘某一个市%‘，索引就不会失效了