Oracle分区表查询未使用本地索引问题排查及优化咨询

问题背景 #

我在Oracle中有一张按MONTHID列分区的分区表SHARED_RAW_RE_GL78，表中约有3亿行数据，每日新增约200万行。我在每个分区的CUST_DIVISION列上创建了本地索引IDX_CUST_DIVISION_1，期望查询时Oracle能使用该索引，但实际执行查询时却进行了全表扫描。

创建索引语句 #

CREATE INDEX IDX_CUST_DIVISION_1 ON "KTC"."SHARED_RAW_RE_GL78" (CUST_DIVISION)
LOCAL
(
    PARTITION MONTHID_01,
    PARTITION MONTHID_02,
    PARTITION MONTHID_03,
    PARTITION MONTHID_04,
    PARTITION MONTHID_05,
    PARTITION MONTHID_06,
    PARTITION MONTHID_07,
    PARTITION MONTHID_08,
    PARTITION MONTHID_09,
    PARTITION MONTHID_10,
    PARTITION MONTHID_11,
    PARTITION MONTHID_12
);

查询语句 #

----the query-----
SELECT GL_CODE, SUM(NET_LCY) 
FROM 
(
  SELECT GL_CODE, NET_LCY 
  FROM "KTC"."SHARED_RAW_RE_GL78"
  WHERE MONTHID=202408 AND CUST_DIVISION = 'KHCN'
) a
GROUP BY GL_CODE;

执行计划 #

从执行计划可见，尽管查询已过滤MONTHID（分区键）和CUST_DIVISION（索引列），Oracle仍对分区8执行全表扫描，未使用CUST_DIVISION列的本地索引。现咨询以下问题：

1. 为何Oracle忽略CUST_DIVISION列的本地索引？
2. 优化器偏好全表扫描的具体原因是什么？
3. 是否有优化器提示或调整手段可强制Oracle使用该索引？

恳请提供索引策略优化建议，谢谢！

问题解答 #

1. Oracle忽略本地索引的原因 #

• 索引覆盖性不足：当前索引仅包含CUST_DIVISION列，查询需要返回GL_CODE和NET_LCY，使用索引后需通过ROWID回表获取这两列数据，额外的回表操作成本被优化器判定为高于全表扫描。
• 统计信息不准确：若表或索引的统计信息过时，优化器无法准确评估索引过滤后的行数及回表成本，可能错误选择全表扫描。
• 过滤后数据占比过高：从执行计划预估的59M行来看，CUST_DIVISION = 'KHCN'在该分区中返回的数据量占比可能很高，优化器认为全表扫描比“索引+回表”更高效。

2. 优化器偏好全表扫描的具体原因 #

Oracle优化器基于成本选择执行计划，偏好全表扫描的核心原因是全表扫描的预估成本低于索引访问的成本：

• 当过滤后的数据量超过分区数据量的10%-20%左右（阈值取决于Oracle版本和系统配置），全表扫描可利用多块读（Multi-Block Read），而索引回表是单块读且需多次随机I/O，前者I/O成本更低。
• 若统计信息显示CUST_DIVISION列基数低（重复值多），优化器会判定索引过滤效果差，选择全表扫描。
• 当前查询需要聚合NET_LCY，全表扫描后直接进行哈希分组，相比索引回表后再聚合，减少了中间数据处理步骤。

3. 强制使用索引的手段 #

• 添加优化器提示：在查询中加入/*+ INDEX(SHARED_RAW_RE_GL78 IDX_CUST_DIVISION_1) */提示，强制优化器使用指定索引。示例：

SELECT GL_CODE, SUM(NET_LCY) 
FROM 
(
  SELECT /*+ INDEX(SHARED_RAW_RE_GL78 IDX_CUST_DIVISION_1) */ GL_CODE, NET_LCY 
  FROM "KTC"."SHARED_RAW_RE_GL78"
  WHERE MONTHID=202408 AND CUST_DIVISION = 'KHCN'
) a
GROUP BY GL_CODE;

• 调整优化器参数：临时调整OPTIMIZER_INDEX_COST_ADJ参数（如设置为10，降低索引的成本权重），但该参数会影响整个会话的优化器行为，需谨慎使用：

ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 10;

索引策略优化建议 #

1. 创建覆盖索引：将查询需要的GL_CODE和NET_LCY加入索引，避免回表操作，让索引直接满足查询需求。创建语句：

CREATE INDEX IDX_CUST_DIVISION_2 ON "KTC"."SHARED_RAW_RE_GL78" (CUST_DIVISION, GL_CODE, NET_LCY)
LOCAL
(
    PARTITION MONTHID_01,
    PARTITION MONTHID_02,
    PARTITION MONTHID_03,
    PARTITION MONTHID_04,
    PARTITION MONTHID_05,
    PARTITION MONTHID_06,
    PARTITION MONTHID_07,
    PARTITION MONTHID_08,
    PARTITION MONTHID_09,
    PARTITION MONTHID_10,
    PARTITION MONTHID_11,
    PARTITION MONTHID_12
);

2. 更新统计信息：确保表和索引的统计信息准确，让优化器做出正确的成本评估：

EXEC DBMS_STATS.GATHER_TABLE_STATS('KTC', 'SHARED_RAW_RE_GL78', CASCADE => TRUE);

3. 检查列基数：若CUST_DIVISION列基数确实很低，该索引价值有限，可考虑结合其他过滤条件创建联合索引，或评估是否需要保留该索引。
4. 验证分区修剪：确认MONTHID=202408正确匹配分区8，避免分区键类型不匹配（如MONTHID是字符串类型但查询用数字）导致分区修剪失效（当前执行计划已显示单分区扫描，此点大概率已排除）。

内容的提问来源于stack exchange，提问作者Thắng Nguyễn