Diagnose the reason why SQL does not go index

From http://www.itpub.net/thread-1852897-1-1.html

There are forum friends in the above post to ask SQL why not go index, just these two days I also just looking at SQL optimization, so try to answer a bit.

Here's the original sql:

Select o.order_id as orderId from 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)

Execution Plan:

The Order_info index is as follows:

• O.is_delete = ' N '--there is no index on this field
• O.commit_time like ' 2014-03-27% '--there is no index on this field
• (O.payment_mode = ' kdfh ' and o.is_paid = ' Y ') or (O.payment_mode = ' HDFK ')--there are indexes on Payment_mode and is_paid, but for condition O . Payment_mode = ' kdfh ' and o.is_paid = ' Y ') if you go through the index, then you need to determine the values of both fields. There is no index based on these two fields, so we cannot go.
• O.order_state not in (+, +)--not in exclude constant, cannot go index

It is recommended to create a combined index on Payment_mode and Is_paid.