Oracle视图结合NOT EXISTS查询失效，环境执行计划差异排查

问题现象 #

• 生产环境（Oracle 12c）执行指定查询时，返回inventory_v视图的全部行，未应用NOT EXISTS过滤；将视图数据复制到静态表后，相同查询返回正确结果。
• 开发环境（Oracle 19c）中相同查询运行正常，执行计划包含NOT EXISTS过滤逻辑，生产环境执行计划无该过滤步骤。

查询语句：

SELECT inv.organization_id, inv.inventory_item_id
  FROM inventory_v inv
 WHERE NOT EXISTS (SELECT 1 
                     FROM inventory_int xx
                    WHERE xx.item_id = inv.inventory_item_id 
                      AND xx.org_id = inv.organization_id
                  );

生产环境执行计划无NOT EXISTS相关谓词，开发环境则在operation id=2处明确有filter( NOT EXISTS (...) )逻辑。

排查方向与解决方案 #

1. 检查统计信息是否过期 #

Oracle优化器依赖准确的统计信息生成执行计划，若inventory_v的基表或inventory_int表统计信息陈旧，可能导致优化器错误判断NOT EXISTS子查询无匹配行，进而跳过过滤逻辑。

执行以下语句收集统计信息：

-- 收集inventory_int表的统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'INVENTORY_INT', CASCADE => TRUE);

-- 收集inventory_v视图依赖的所有基表的统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => '基表1名称', CASCADE => TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => '基表2名称', CASCADE => TRUE);

-- 或批量收集整个schema的统计信息
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => '你的用户名', CASCADE => TRUE, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE);

2. 验证列数据类型是否匹配 #

若inventory_v的inventory_item_id/organization_id与inventory_int的item_id/org_id数据类型不匹配（如一个为NUMBER，一个为VARCHAR2），隐式转换会导致优化器无法正确评估关联条件，可能误认为无匹配行，使NOT EXISTS始终为真。

查询列数据类型：

SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH
FROM ALL_TAB_COLUMNS 
WHERE TABLE_NAME IN ('INVENTORY_INT', '基表1名称', '基表2名称') -- 替换为inventory_v的基表
  AND COLUMN_NAME IN ('INVENTORY_ITEM_ID', 'ORGANIZATION_ID', 'ITEM_ID', 'ORG_ID');

若存在类型不匹配，需调整表结构统一类型，或在查询中显式转换（如TO_NUMBER(xx.item_id)）。

3. 测试优化器Hint强制逻辑 #

Oracle 12c存在部分优化器bug，可能导致视图与NOT EXISTS组合时的逻辑转换错误。尝试用Hint强制优化器保留NOT EXISTS逻辑：

SELECT /*+ NO_MERGE(inv) NOT_EXISTS */ 
       inv.organization_id, inv.inventory_item_id
  FROM inventory_v inv
 WHERE NOT EXISTS (SELECT 1 
                     FROM inventory_int xx
                    WHERE xx.item_id = inv.inventory_item_id 
                      AND xx.org_id = inv.organization_id
                  );

若添加Hint后查询结果正确，说明是优化器转换问题，可临时用Hint规避，或升级Oracle补丁至最新版本。

4. 对比开发与生产环境的优化器参数 #

优化器参数差异可能导致执行计划生成逻辑不同，检查以下关键参数：

SELECT NAME, VALUE 
FROM V$PARAMETER 
WHERE NAME IN ('optimizer_mode', 'optimizer_features_enable', 'optimizer_adaptive_features');

若生产环境optimizer_features_enable为旧版本（如12.1.0.2），可尝试临时设置为19c兼容模式测试：

ALTER SESSION SET optimizer_features_enable = '19.1.0';

若测试后执行计划正常，可考虑调整参数或升级数据库版本。

5. 检查视图定义是否存在差异 #

确认开发与生产环境中inventory_v的视图定义完全一致，避免生产环境视图包含额外逻辑（如与inventory_int的隐式关联）导致NOT EXISTS条件失效：

SELECT TEXT FROM ALL_VIEWS WHERE VIEW_NAME = 'INVENTORY_V';

对比开发与生产的视图文本，确保无差异。

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