WHERE子句应该注意的方面--来自以前培训资料

where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么就可以被SQL优化器优化，使用索引，避免表搜索
例子：
写法1：WHERE CMCC_BRANCH_CD LIKE ‘GZ%’
写法2：WHERE LEFT (CMCC_BRANCH_CD, 2) = ‘GZ’
比较：建议使用第一种写法，因为它允许使用索引。而第二种写法只能用表扫描
如下：ref.TR_CELL有主键:+LOCAL_CD+CELL_CD,以下例子如果是选count(*) 则例外。
select *  from ref.TR_CELL  where local_cd like ‘_57%' with ur; --没用到索引
select *  from ref.TR_CELL  where left(local_cd,3)=‘257’ with ur; --没用到索引
select *  from ref.TR_CELL  where substr(local_cd,1,3)='257' with ur; --用到索引
select *  from ref.TR_CELL  where local_cd like '257%' with ur; --用到索引
2.WHERE后面的条件顺序影响
WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响，如
Select * from ods.to_usr where cmcc_branch_cd= ‘GZ01' and usr_sts_cd =1
Select * from ods.to_usr where usr_sts_cd=1 'and cmcc_branch_cd= ‘GZ01’
以上两个SQL中cmcc_branch_cd及usr_sts_cd两个字段都没进行索引，所以执行的时候都是全表扫描，如果：第一条SQL的cmcc_branch_cd= ‘GZ01'条件在记录集内比率为99%，而usr_sts_cd=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行cmcc_branch_cd及usr_sts_cd的比较，而在进行第二条SQL的时候0.5%条记录都进行cmcc_branch_cd及usr_sts_cd的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。因此where 中把比率小的条件放前面！

 

可利用索引的谓词：

PREDICATE TYPE

INDEXABLE

STAGE 1

INDEXABLE

COL = value

Y

Y

COL = noncol expr

Y

Y

COL IS NULL

Y

Y

COL op value

Y

Y

COL op noncol expr

Y

Y

COL BETWEEN value1 AND value2

Y

Y

COL BETWEEN noncol expr1 AND noncol expr2

Y

Y

COL LIKE 'pattern' (pattern 是 xxx%)

Y

Y

COL IN (list)

Y

Y

COL LIKE host variable

Y

Y

T1.COL = T2.COL

Y

Y

T1.COL op T2.COL

Y

Y

COL = (non subq)

Y

Y

COL op ANY(non subq)

Y

Y

COL op ALL(non subq)

Y

Y

COL IN (non subq)

Y

Y

COL = expression

Y

Y

(COL1,…COLn) IN (non subq)

Y

Y

不可利用索引的谓词：

PREDICATE TYPE

INDEXABLE

STAGE 1

NON-INDEXABLE

COL <> value

N

Y

COL <> noncol expr

N

Y

COL IS NOT NULL

N

Y

COL NOT BETWEEN value1 AND value2

N

Y

COL NOT BETWEEN noncol expr1 AND noncol expr2

N

Y

COL NOT IN(list)

N

Y

COL NOT LIKE 'char'

N

Y

COL LIKE '%char'

N

Y

COL LIKE '_char'

N

Y

T1.COL <> T2.COL

N

Y

T1.COL1 = T1.COL2

N

Y

COL <> (non subq)

N

Y