如何优化含多个AND与LIKE语句的Oracle大表查询

你的查询执行慢的核心问题在于对Co_Acc列频繁使用Substr函数——这种操作会让Oracle无法利用该列上的常规B树索引（如果存在的话），只能被迫走全表扫描，面对大表时自然耗时飙升。下面是针对性的优化方案：

1. 改写Co_Acc过滤条件，避免列上的函数调用 #

把依赖函数的条件改成直接匹配原列，让索引有机会发挥作用：

针对Substr(t.Co_Acc, 8) LIKE '12294%' #

这个条件等价于Co_Acc LIKE '_______12294%'（注意是7个下划线，对应从第8位开始匹配前缀）。这样改写后，Oracle可以直接使用Co_Acc列上的B树索引做前缀匹配，无需全表扫描。

针对Substr(t.Co_Acc, -3)相关的范围条件 #

原条件是筛选最后三位在600-695之间且不等于683的记录。如果Co_Acc的最后三位是数字格式的字符串，直接用字符串比较可能会有排序问题（比如'060'作为字符串比'599'小，但转成数值是60，符合>599的要求），所以建议转成数值后再过滤：

TO_NUMBER(RIGHT(t.Co_Acc, 3)) BETWEEN 600 AND 695 
AND TO_NUMBER(RIGHT(t.Co_Acc, 3)) != 683

如果想让这个条件也能用上索引，可以创建函数索引：

CREATE INDEX idx_op_history_coacc_last3 ON operation_history(TO_NUMBER(RIGHT(Co_Acc, 3)));

如果业务允许，更推荐把Co_Acc的最后三位单独提取成一个数值列（比如co_acc_last3），这样查询时直接用该列过滤，既避免函数转换开销，又能完美利用常规索引。

2. 创建高效的复合索引 #

根据你的过滤条件，建议创建包含state_id、curr_day和Co_Acc的复合索引（顺序按列的选择性调整，选择性高的列放前面）：

CREATE INDEX idx_op_history_state_currday_coacc ON operation_history(state_id, curr_day, Co_Acc);

如果希望彻底避免回表查询，可以创建覆盖索引——把查询需要返回的列也包含进去，Oracle直接从索引中获取数据，无需访问表：

CREATE INDEX idx_op_history_cover ON operation_history(
    state_id, 
    curr_day, 
    Co_Acc, 
    co_filial, 
    emp_birth, 
    Sum_Pay
);

3. 优化后的完整查询语句 #

整合所有改写后的最终查询：

SELECT 
    t.co_filial as fil_code, 
    t.emp_birth as emp_code, 
    to_char(t.curr_day, 'YYYY-MM-DD') as operation_date, 
    TRUNC(t.Sum_Pay/100) As summa 
FROM operation_history t 
WHERE 
    t.Co_Acc LIKE '_______12294%' 
    AND TO_NUMBER(RIGHT(t.Co_Acc, 3)) BETWEEN 600 AND 695 
    AND TO_NUMBER(RIGHT(t.Co_Acc, 3)) != 683 
    AND t.state_id = 41 
    AND t.curr_day >= to_date('12.08.2019', 'DD.MM.YYYY') 
    AND t.curr_day < to_date('13.08.2019', 'DD.MM.YYYY');

4. 额外的优化小贴士 #

• 更新表统计信息：如果统计信息过时，Oracle可能会选择低效的执行计划，执行EXEC DBMS_STATS.GATHER_TABLE_STATS('你的用户名', 'OPERATION_HISTORY');来更新。
• 简化日期转换：如果前端可以直接处理日期类型，建议去掉to_char转换，直接返回curr_day，减少数据库计算开销。

内容的提问来源于stack exchange，提问作者Abdusoli