绑定变量导致执行计划不走索引

客户反应有个查询慢，看了一下没有走索引，以为很简单，结果发现和以前遇到的问题完全不一样，原来是 bind peeking问题，最后只好建议客户用hint，以下是过程。

explain plan for select pcode as ID,

       tjDate as riqi,

       busiarea as dishiID,

       a.agentno as qudaoID,

       a.patch pianquID,

       branch yewuquID,

       inputway as jierufangshiID,

       clienttype wangluoleixingID,

       status zhuangtaiID,

       nums as shuliang

  from stat_cm a

    where (a.tjDate = to_date('2013-12-01', 'yyyy-mm-dd') or

       '2013-12-01' is null)

   and (instr('X~B~D~W~T~Y~L~H~A~Z~S~C~R~E~G', busiArea) <> 0 or

       trim('X~B~D~W~T~Y~L~H~A~Z~S~C~R~E~G') = '*' or

       'X~B~D~W~T~Y~L~H~A~Z~S~C~R~E~G' is null)

   and (instr('001250~002773~006411~006431', pcode) <> 0 or

       trim('001250~002773~006411~006431') = '*' or

       '001250~002773~006411~006431' is null)

   and (instr('0~1~2', clienttype) <> 0 or trim('0~1~2') = '*' or

       '0~1~2' is null)

   and (instr('0~1~2', inputway) <> 0 or trim('0~1~2') = '*' or

       '0~1~2' is null);

       

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------

Plan hash value: 962090180

------------------------------------------------------------------------------

--------------

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CP

U)| Time     |

------------------------------------------------------------------------------

--------------

|   0 | SELECT STATEMENT            |              |     1 |    35 |    13   (

0)| 00:00:01 |