--oracle索引失效
--创建测试表
create table t(
empno number,
ename varchar2(20),
deptno number
);
--创建索引
create index idx_deptno on t(deptno);
--创建复合索引
create index idx_empno_deptno on t(empno,deptno);
drop index idx_empno_deptno;
create index idx_empno_deptno on t(deptno,empno);
--插入测试数据
insert into t values(1001,'A',10);
1 <>
SQL> select * from t where deptno=10;
Execution Plan
----------------------------------------------------------
Plan hash value: 3776569808
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 76 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 2 | 76 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_DEPTNO | 2 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
SQL> select * from t where deptno<>10;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 266 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 7 | 266 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
2 模糊查询 like '%_'
SQL> select * from t where deptno like '%_1';
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 38 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
3 单独引用复合索引里非第一位置的索引列.
SQL> select * from t where empno=1005;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 38 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
4 字符型字段为数字时在where条件里不添加引号
alter table t add id varchar2(20);
create index idx_id on t(id);
SQL> select * from t where id=33;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 50 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 50 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> select * from t where id='33';
Execution Plan
----------------------------------------------------------
Plan hash value: 827754323
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 50 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 50 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_ID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
5 对索引列进行运算.需要建立函数索引
SQL> select * from t where deptno*2=20;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 100 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 2 | 100 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> select * from t where substr(deptno,1,1)=3;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 50 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 50 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
6 not in ,not exist.
SQL> select * from t where deptno not in ('10','20','30','40','50','60');
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 150 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 3 | 150 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
7 基于cost成本分析(oracle因为走全表成本会更小)：查询小表,或者返回值大概在10%以上
8 B-tree索引 is null不会走,is not null会走
SQL> select * from t where id is null;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 350 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 7 | 350 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> select * from t where id is not null;
Execution Plan
----------------------------------------------------------
Plan hash value: 875909553
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 150 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 3 | 150 | 2 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN | IDX_ID | 10 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
索引失效总结：
1.没有查询条件，查询条件没有建立索引
2.查询条件没有使用索引引导列
3.基于cost优化器，查询结果集30%以上
4.索引本身失效
5.查询条件使用函数在索引列上
6.隐式转换
7.模糊查询
8.not in , null ,<>
