Oracle升级后带日期比较的COUNT(*)聚合查询返回0记录问题

优先排查方向：统计信息异常或11g优化器新特性触发错误查询转换 #

Oracle 11g引入了多项优化器新特性，升级后旧版本的统计信息未同步更新、或者优化器转换逻辑出错是这类count查询结果异常的高发原因：

• 首先执行以下命令刷新相关对象的统计信息：

-- 刷新视图依赖基表、关联表的统计信息，需替换为实际的用户名和CPRODUCT对应的基表名
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'CPRODUCT对应的基表名', CASCADE => TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'CATEGORY', CASCADE => TRUE);
-- 刷新视图统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'CPRODUCT', CASCADE => TRUE);

执行完成后重新运行count查询验证结果是否正常。

• 临时禁用优化器查询转换验证是否是特性BUG：
  在count查询前加hint强制禁用查询重转换，执行后看结果是否正确：

SELECT /*+ NO_REWRITE */ COUNT(*)
FROM "CPRODUCT"
INNER JOIN "CATEGORY" ON CATEGORY.ID = CPRODUCT.PID
WHERE ( lastdate BETWEEN '27-OCT-21' AND '27-OCT-21')
and ( "ACGCODE" ='01150');

如果加hint后返回结果正确，说明触发了11.2.0.4版本的已知优化器BUG，常见的相关BUG编号为17551261、18467579，可通过安装对应补丁修复，临时解决方案可设置会话/系统级参数：

ALTER SESSION SET "_optimizer_squ_bottomup" = FALSE;
-- 如需全局生效则执行以下命令，执行后需重启数据库实例
ALTER SYSTEM SET "_optimizer_squ_bottomup" = FALSE SCOPE=SPFILE;

其次排查：索引逻辑损坏 #

count(*)查询默认会优先选择最小的可用索引做快速全扫描，而select *会走全表扫描，如果索引存在逻辑损坏会出现两者结果不一致的情况：

• 验证索引有效性：

-- 检查CATEGORY表、CPRODUCT基表上相关索引的状态，需替换为实际的CPRODUCT基表名
SELECT INDEX_NAME, STATUS FROM USER_INDEXES WHERE TABLE_NAME IN ('CATEGORY','CPRODUCT基表名');
-- 重建状态不正常的索引，或者直接重建所有关联索引
ALTER INDEX 索引名 REBUILD;

重建完成后重新执行count查询验证。

最后排查：日期隐式转换异常 #

你SQL中的日期用的是字符串字面量'27-OCT-21'，依赖NLS_DATE_FORMAT的配置，不同执行计划走不同的会话上下文读取时可能出现隐式转换偏差，建议修改为标准的日期字面量写法避免这类问题：

-- 改为DATE关键字声明的标准日期格式
WHERE lastdate BETWEEN DATE '2021-10-27' AND DATE '2021-10-27'

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