DEFAULT NULL索引失效 sql is null 索引

针对网上sql优化验证

环境:oracle 11

1. IS NULL 与 IS NOT NUL

　　不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。

　　任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。
验证:

//表结构
create table TEST_BM
(
  deptno VARCHAR2(14)  Primary  not null,
  dname  VARCHAR2(14),
  loc    VARCHAR2(13)
)
//增加一个索引
create index TEST_BM_NAME on TEST_BM (DNAME);

//查询语句 验证is null
select * from TEST_BM a where a.dname is  null

从解释计划中可以看出来虽然dname有索引，但是还是走了全表扫描

//查询语句 验证is not null
select * from TEST_BM a where a.dname is not null

从解释计划中可以看出来虽然dname有索引，但是还是走了全表扫描

---

2. 对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的

select * from TEST_BM a where a.dname||'U' ='22K9SbKb6lXhUU'

从上图解释计划中，很明显的看到走的是全表扫描

---

3.带通配符(%)的like语句

select * from TEST_BM a where a.dname like '%2mDYhbiVY8hcP%'

前后都有%的走的是全表扫描

select * from TEST_BM a where a.dname like '2mDYhbiVY8hcP%'

只是在后面有%走的是索引，但是也不一定，如果查询的结果很多，oracle会选择走全表扫描比如select * from TEST_BM a where a.dname like ‘2%’

---

4.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
这个确实是这样的，但是这个东西只能说尽量避免。

5.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描

select * from TEST_BM a where a.dname = '2mDYhbiVY8hcP' or a.loc='30ftf7IL6w0cx'

分析:因为loc没有索引，所以这条语句还是走了全表扫描。

可以改写成

select * from TEST_BM a where a.dname = '2mDYhbiVY8hcP' 
union all
select * from TEST_BM a where a.loc='30ftf1IL6w0cx'

这样上面的会走索引

6.in 和 not in 也要慎用，否则会导致全表扫描

select * from TEST_BM a where a.dname  in('2mDYhbiVY8hcP','21hz6uVXa4TTX','2aH5RPcQcsMdG','2aPpfZplfVTr2','2LUkuqXN9VKPg','2HgvKroBnvjkM')

可以看到in是会走索引的，而且oracle 内容把in翻译成or来处理

select * from TEST_BM a where a.dname not in('2mDYhbiVY8hcP','21hz6uVXa4TTX')

not in是一定不走缩印的，not in在oracle内部处理方式 != <> 前面也讲到 慎用 != <>

7.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引

select * from TEST_BM a where substr(a.dname,0,2)='22'

//可以改写成
select * from TEST_BM a where a.dname like '22%'