某分区的索引失效导致绑定变量查询都无法使用索引

场景描述

Oracle中有一张订单表，按照时间字段分区，且在时间字段上存在本地分区索引，其表定义如下：

CREATE TABLE ORDER
(
ID CHAR(36) NOT NULL,
CREATE_TIME DATE
AMOUNT NUMBER(15,2)
)
PARTITION BY RANGE (CREATE_TIME)
(
PARTITION PART2015 VALUES LESS THAN (TO_DATE('2015-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) TABLESPACE TS_MA1,
PARTITION PART2016 VALUES LESS THAN (TO_DATE('2016-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) TABLESPACE TS_MA2,
PARTITION PART2017 VALUES LESS THAN (TO_DATE('2017-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) TABLESPACE TS_MA3
);
CREATE INDEX IDX_ORDER ON ORDER(CREATE_TIME) LOCAL;

现在要统计20170101-20170123之间的订单，查询语句为：

SELECT COUNT(1) 
 FROM ORDER
 WHERE CREATE_TIME BETWEEN
      TO_DATE(&ST,'YYYY-MM-DD HH24:MI:SS')
  AND TO_DATE(&ET, 'YYYY-MM-DD HH24:MI:SS');

在程序中使用PreparedStatement进行绑定变量查询时，执行效率十分低下，但是把该SQL中的变量替换为值后再次执行查询后，查询性能得到大大提升。

 

定位分析

在PL/SQL Developer 命令窗口中分别查看绑定变量和未绑定变量的SQL的执行计划，发现不含绑定变量的SQL的执行计划中单分区上的索引扫描 （INDEX RANGE SCAN和PARTITION RANGE SINGLE）在绑定变量后变成基于分区遍历的全表扫描（TABLE ACCESS FULL和PARTITION RANGE ITERATOR），这是导致查询性能大大下降的原因。
那么,为什么执行计划没有走分区索引而是全表扫描呢？执行下列语句查询分区索引：

SELECT * FROM USER_IND_PARTITIONS T WHERE INDEX_NAME='IDX_ORDER';

发现分区PART2015上的分区索引为不可用状态（STATUS=UNUSABLE）。突然间恍然大悟，因为非绑定变量的方式Oracle在SQL解析期能够明确定位到要访问的分区(即静态分区消除)继而可以判断作用在该分区上的索引是否有效，该次查询中输入的查询条件命中了一个可用分区，所以执行计划采用了单分区上的索引扫描；而绑定变量的方式因为要依赖变量的输入值，在存在分区索引不可用的情况下，Oracle在SQL解析期无法判断查询要作用的分区上对应的分区索引是否有效，所以执行计划采用了遍历全部分区以定位到目标分区(即动态分区消除)并在目标分区上执行全表扫描的方式。

解决方案

• 第一种方法是重建失效的索引分区

    ALTER INDEX IDX_ORDER REBUILD PARTITION PART2015;

• 第二种方法是采用hint方式强制Oracle走索引扫描，但是需要注意的是，若绑定变量的值指向了不可用的分区会导致查询执行失败

   SELECT /*+ INDEX(ORDER IDX_ORDER ) */ COUNT(1) 
     FROM ORDER
    WHERE CREATE_TIME BETWEEN
          TO_DATE(&ST,'YYYY-MM-DD HH24:MI:SS')
     AND TO_DATE(&ET, 'YYYY-MM-DD HH24:MI:SS');