往期回顾：

列的空值约束设置是影响Oracle优化器索引选择策略的关键因素。本文作为《左右SQL执行计划妙招》系列第十五篇，通过实际案例展示NOT NULL约束如何改变COUNT(*)查询的执行计划。

环境准备：构建测试表和索引

drop table t purge;
-- 创建测试表，过滤掉空值数据
create table t as select * from dba_objects where object_id is not null;
-- 在object_id列上创建索引
create index idx_object_id on t(object_id);
set linesize 1000
set pagesize 2000
set autotrace traceonly

对比实验：空值约束对执行计划的影响

语句1：索引列允许空值的查询

-- 语句1（object_id列允许为空）
select count(*) from t;

执行计划：

-------------------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   292   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 67030 |   292   (1)| 00:00:04 |
-------------------------------------------------------------------
统计信息
-------------------------------------------------------------------
          0  recursive calls
          0  db block gets
       1047  consistent gets
          0  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

语句2：添加NOT NULL约束后的查询

-- 设置object_id列为NOT NULL
alter table t modify object_id not null;
-- 语句2（相同SQL，但object_id列已设置为NOT NULL）
select count(*) from t;

执行计划：

-------------------------------------------------------------------------------
Plan hash value: 1131838604
-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 |    49   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_OBJECT_ID | 67030 |    49   (0)| 00:00:01 |
-------------------------------------------------------------------------------
统计信息
-------------------------------------------------------------------------------
          0  recursive calls
          0  db block gets
        170  consistent gets
          0  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

核心差异分析

执行计划对比

在这里插入图片描述

优化器决策逻辑

关键技术原理： Oracle B树索引不存储完全为NULL的条目，当列允许空值时，优化器无法确保索引包含所有记录，因此选择全表扫描保证COUNT(*)结果准确性。

NOT NULL约束的价值：约束确保索引包含所有记录，优化器可以安全选择索引快速扫描，显著减少IO操作。

实际应用指南

约束添加的前提条件

业务逻辑评估：添加NOT NULL约束必须符合实际业务规则，不能仅为性能优化而强制约束。

-- 正确示例：业务规则本身要求非空
-- 订单ID在业务逻辑上不可能为空
ALTER TABLE orders MODIFY order_id NOT NULL;
-- 客户ID对于有效订单记录是必需的
ALTER TABLE orders MODIFY customer_id NOT NULL;
-- 错误示例：强制添加不符合业务逻辑的约束
-- 如果评论内容在业务上允许为空，则不应添加约束
-- ALTER TABLE comments MODIFY content NOT NULL; -- 错误做法

总结

NOT NULL约束是查询性能优化的重要手段，但必须在符合业务逻辑的前提下使用。本案例显示，合理的约束设置将COUNT(*)查询的逻辑读从1047次减少到170次，性能提升超过80%。在数据库设计中，应首先确保约束符合业务规则，然后利用这些约束获得查询性能的额外收益。

在这里插入图片描述

未完待续..

公众号：收获不止数据库

微信：ljbyxl1213

系列回顾

ITPub博客

左右SQL执行计划妙招 ⑮ ——执行计划利用设计特性改变【列空值影响】

环境准备：构建测试表和索引

对比实验：空值约束对执行计划的影响

核心差异分析

优化器决策逻辑

实际应用指南

总结

最新文章