
场景描述
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');