诊断一句SQL不走索引的原因
from http://www.itpub.net/thread-1852897-1-1.html
有论坛朋友在上面的帖子里问SQL为什么不走索引,正好这两天我也刚刚在看SQL优化，于是试着回答了一下.
下面是原来的SQL:
select o.order_id as orderIdfrom order_info o, member m
where m.member_id = o.member_id
and o.is_delete = 'N'
/*and (to_date(nvl(o.paid_time,o.commit_time), 'YYYY-MM-DD hh24:mi:ss') >=
to_date('2014-03-27 00:00:00', 'YYYY-MM-DD hh24:mi:ss'))
and (to_date(nvl(o.paid_time,o.commit_time), 'YYYY-MM-DD hh24:mi:ss') <=
to_date('2014-03-27 23:59:59', 'YYYY-MM-DD hh24:mi:ss'))*/
and o.commit_time like '2014-03-27%'
and ((o.payment_mode = 'KDFH' and o.IS_PAID = 'Y') or
(o.payment_mode = 'HDFK'))
and o.order_state NOT in (18, 19, 25)
and (exists (select 1
from fecerp.sys_role_select rs, fecerp.sys_role_user su
where su.role_id = rs.role_id
and su.user_id = 3132
and su.is_delete = 'N'
and rs.othera = 0) OR exists
(select 1
from fecerp.sys_role_select rs,
fecerp.sys_role_user su,
product_yw_catalog pyc,
product_yw_catalog_goods ycg,
order_item oi
where su.role_id = rs.role_id
and pyc.yw_catalog_id = ycg.wy_catalog_id
and su.user_id = 3132
and rs.othera <> 0
and su.is_delete = 'N'
and ',' || rs.bus_ids || ',' like
'%,' || pyc.yw_catalog_id || ',%'
and oi.order_id = o.order_id
and oi.is_delete = 'N'
and ycg.goods_no = oi.goods_no))
and (select m.multi_channel_id
from ec_multi_channel m
where m.multi_channel_id = o.multi_channel_id) in
(select ser.multi_channel_id
from fecerp.sys_role_channel ser, fecerp.sys_role_user uss
where ser.role_id = uss.role_id
and ser.is_delete = 'N'
and uss.is_delete = 'N'
and uss.user_id = 3132)
执行计划：
order_info 索引如下：
和统计信息的关系不大，至少表order_info 的统计信息是正确的，否则table full access的cost不会这么高.
不走索引应该是下面的原因.
- o.is_delete = 'N' --这个字段上没有索引
- o.commit_time like '2014-03-27%'--这个字段上没有索引
- ((o.payment_mode = 'KDFH' and o.IS_PAID = 'Y') or (o.payment_mode = 'HDFK'))--在payment_mode 和 IS_PAID 上虽然分别有索引,但是 对于条件 o.payment_mode = 'KDFH' and o.IS_PAID = 'Y') 如果走索引，那么是需要同时判断这两个字段的值. 这里没有基于这两个字段的索引，所以也走不了.
- o.order_state NOT in (18, 19, 25) -- not in 排除常量,走不了索引
推荐在 payment_mode和 IS_PAID 上建立一个组合索引.
优质内容筛选与推荐>>1、codeforces 706C
2、php http Manual
3、电子科大POJ "孤单整数"
4、HDU-5806 NanoApe Loves Sequence Ⅱ（two-pointer或二分）
5、Ubuntu Server安全Webserver搭建流程