设置NOT NULL约束后，Oracle执行IS NULL查询仍走TABLE ACCESS (FULL)

问题场景 #

我有一张约40万行的MyTable表，MyColumn未定义NOT NULL但存在UNIQUE KEY约束。执行查询SELECT ... FROM MyTable WHERE MyColumn IS NULL时，Oracle执行TABLE ACCESS (FULL)全表扫描，这符合预期——毕竟列没有非空约束，优化器需要扫表确认是否存在NULL值。

但给MyColumn添加NOT NULL约束后，Oracle仍然执行全表扫描。按道理，Oracle应该能识别该列不可能有NULL值，直接返回空结果即可，没必要执行扫表操作，不是吗？

补充发现（编辑后） #

对比SQL Developer和DBeaver的执行计划后发现，Oracle会区分NOT NULL约束是否带有NOVALIDATE选项：

• 不带NOVALIDATE时，执行计划会出现相关提示，表明Oracle已识别到约束，明确知道MyColumn IS NULL不会有匹配数据
• 带NOVALIDATE时，执行计划和未设置NOT NULL约束时完全一致

这说明NOT NULL约束确实能影响优化器的判断，只是NOVALIDATE选项会让优化器无法信任现有数据符合约束要求。

原因拆解 #

Oracle优化器能否跳过全表扫描，核心在于它是否能100%确认现有数据完全符合约束规则：

• 添加NOT NULL约束时不带NOVALIDATE：Oracle会先检查全表所有数据，确保不存在NULL值，之后优化器可以完全信任该约束，直接判定WHERE MyColumn IS NULL无匹配结果，无需扫表
• 添加约束时带NOVALIDATE：Oracle仅保证后续插入/更新的数据符合约束，不会检查已存在的历史数据。这种情况下，优化器无法确定旧数据中是否存在NULL值，只能执行全表扫描来验证条件

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