场景描述

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');

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.