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

客户反应有个查询慢，看了一下没有走索引，以为很简单，结果发现和以前遇到的问题完全不一样，原来是 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 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| STAT_CM      |     1 |    35 |    13   (

0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | STAT_CM_IDX1 |     1 |       |    12   (

0)| 00:00:01 |

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

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

Predicate Information (identified by operation id):

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

   1 - filter(INSTR('0~1~2',"INPUTWAY")<>0)

   2 - access("A"."TJDATE"=TO_DATE(' 2013-12-01 00:00:00', 'syyyy-mm-dd

              hh24:mi:ss'))

       filter(INSTR('X~B~D~W~T~Y~L~H~A~Z~S~C~R~E~G',"BUSIAREA")<>0 AND

              INSTR('001250~002773~006411~006431',"PCODE")<>0 AND INSTR('0~1~2

',"CLIENTTYPE")<>0)

explain plan for SELECT  pcode     AS ID ,

        tjDate    AS riqi ,

        busiarea  AS dishiID ,

        a.agentno AS qudaoID ,

        a.patch pianquID      ,

        branch yewuquID      ,

        inputway AS jieruID,

        clienttype wangluoID ,

        status zhuangtaiID       ,

        nums AS shuliang

FROM    stat_cm a

WHERE

        (

                a.tjDate = to_date(:1, 'yyyy-mm-dd')

                OR :2   IS NULL

        )

        AND

        (

                instr(:3, busiArea) <> 0

                OR trim(:4)          = '*'

                OR :5               IS NULL

        )

        AND

        (

                instr(:6, pcode) <> 0

                OR trim(:7)       = '*'

                OR :8            IS NULL

        )

        AND

        (

                instr(:9, clienttype) <> 0

                OR trim(:10)           = '*'

                OR :11                IS NULL

        )

        AND

        (

                instr(:12, inputway) <> 0

                OR trim(:13)          = '*'

                OR :14               IS NULL

        ) ;

        

        

PLAN_TABLE_OUTPUT

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

Plan hash value: 1590340904

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

| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |         |    23 |   805 |  2170  (16)| 00:00:27 |

|*  1 |  TABLE ACCESS FULL| STAT_CM |    23 |   805 |  2170  (16)| 00:00:27 |

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

Predicate Information (identified by operation id):

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

   1 - filter((:5 IS NULL OR INSTR(:3,"BUSIAREA")<>0 OR TRIM(:4)='*')

              AND (:8 IS NULL OR INSTR(:6,"PCODE")<>0 OR TRIM(:7)='*') AND (:1

1 IS

              NULL OR INSTR(:9,"CLIENTTYPE")<>0 OR TRIM(:10)='*') AND (:14 IS

NULL OR

              INSTR(:12,"INPUTWAY")<>0 OR TRIM(:13)='*') AND (:2 IS NULL OR

              "A"."TJDATE"=TO_DATE(:1,'yyyy-mm-dd')))

              

我草果然不走索引

select a.owner,a.index_name,a.INDEX_TYPE,b.column_name from dba_indexes a,dba_ind_columns b where a.table_name=upper('STAT_CM') and b.index_name=a.index_name;

OWNER                          INDEX_NAME                     INDEX_TYPE

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

COLUMN_NAME

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

EDS                            STAT_CM_IDX1                   NORMAL

TJDATE

EDS                            STAT_CM_IDX1                   NORMAL

PCODE

EDS                            STAT_CM_IDX1                   NORMAL

CLIENTTYPE

这确实是一个普通索引

  CREATE INDEX "EDS"."STAT_CM_IDX1" ON "EDS"."STAT_CM" ("TJDATE", "PCODE", "CLIENTTYPE")

  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

  STORAGE(INITIAL 2097152 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "EDS_TBS"

explain plan for SELECT  pcode     AS ID ,

        tjDate    AS riqi ,

        busiarea  AS dishiID ,

        a.agentno AS qudaoID ,

        a.patch pianquID      ,

        branch yewuquID      ,

        inputway AS jieruID,

        clienttype wangluoID ,

        status zhuangtaiID       ,

        nums AS shuliang

FROM    stat_cm a

WHERE

        (

                tjDate = to_date(:1, 'yyyy-mm-dd')

                OR :2   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: 1590340904

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

| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |         |     1 |    35 |  2036  (10)| 00:00:25 |

|*  1 |  TABLE ACCESS FULL| STAT_CM |     1 |    35 |  2036  (10)| 00:00:25 |

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

Predicate Information (identified by operation id):

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

PLAN_TABLE_OUTPUT

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

   1 - filter(INSTR('X~B~D~W~T~Y~L~H~A~Z~S~C~R~E~G',"BUSIAREA")<>0 AND

              INSTR('001250~002773~006411~006431',"PCODE")<>0 AND

              INSTR('0~1~2',"CLIENTTYPE")<>0 AND INSTR('0~1~2',"INPUTWAY")<>0 AN

D (:2

              IS NULL OR "TJDATE"=TO_DATE(:1,'yyyy-mm-dd')))

16 rows selected.

= =看来只有那个日期的改了才会走，不知道为什么死活不走

############################################结论##########################

还是不行，推测是bind peeking导致

这样只能用hint来使其强制走索引，或者禁用peeking功能。

也可以使用alter system flush shared_pool;--清理share_pool

重新进行执行计划碰碰运气

另外百度了一下，is not null如果写得好一定程度下也走索引，不过其实效率也是低的，因为索引里面所有值都是非空