Oracle日期比较查询自动取消问题排查求助

以下是针对问题的具体优化方案：

• 避免查询字段上的函数调用，利用索引
  TRUNC(created)会导致CREATED字段上的索引失效，强制全表扫描。将日期条件改写为字段直接比较的形式，让索引生效：

  -- 等价于TRUNC(created) > '2025-01-23'，保留字段原始值以命中CREATED索引
  CREATED >= TRUNC(TO_DATE('2025-01-23', 'YYYY-MM-DD')) + INTERVAL '1' DAY
  

  若CREATED为DATE类型，可简化为：

  CREATED >= DATE '2025-01-24'
  

• 创建复合覆盖索引
  若现有索引无法覆盖查询需求，创建包含过滤、排序和返回字段的覆盖索引，避免回表操作：

  CREATE INDEX idx_foo_created_field3_inc ON foo(CREATED, FIELD3)
  INCLUDE (FIELD1, FIELD2, FIELD4, FIELD5);
  

  该索引可直接提供查询所需的所有数据，无需访问表本身，大幅提升性能。

• 调整ROWNUM与ORDER BY的执行顺序
  原查询中ROWNUM <=10会在ORDER BY前生效，可能导致优化器扫描大量数据后再排序。改为先排序再取前10条的子查询写法，利用索引有序性快速返回结果：

  SELECT *
  FROM (
      SELECT 
          CREATED, FIELD1, FIELD2, FIELD3, FIELD4, FIELD5
      FROM foo
      WHERE CREATED >= DATE '2025-01-24'
            AND FIELD3 IS NOT NULL
            AND CREATED IS NOT NULL
      ORDER BY CREATED ASC
  )
  WHERE ROWNUM <= 10;
  

• 更新表统计信息
  过时的统计信息会导致优化器选择不合理的执行计划，手动收集最新统计信息：

  EXEC DBMS_STATS.GATHER_TABLE_STATS('你的用户名', 'FOO', CASCADE => TRUE);
  

• 切换优化器模式
  针对仅需少量数据的查询，临时切换优化器模式为优先返回前N行：

  ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10;
  

• 检查字段类型与数据质量
  确认CREATED为DATE类型而非VARCHAR2类型。若为字符串类型，日期比较会触发全表扫描，建议修改字段类型为DATE。

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