启用NOT NULL 约束条件与 HASH JOIN / FILTER

最新推荐文章于 2025-06-21 17:13:23 发布

原创最新推荐文章于 2025-06-21 17:13:23 发布 · 418 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #优化

oracle开发优化细节专栏收录该内容

2 篇文章

订阅专栏

本文通过实验对比了SQL查询中字段是否设置NOT NULL约束对执行计划的不同影响，发现有NOT NULL约束时Oracle偏好使用hashjoin，而无约束时则倾向于filter优化的nestloop。

最近在书上看到一个说法，字段有无not null 约束对于查询方式的选择有影响，但未给出具体的实例。于是秉承自己动手丰衣足食的思想，做了一个简单的实验来看看到底有什么不同。

create table A
(
  id   NUMBER not null,
  name VARCHAR2(10) not null
)

insert into a values(1,'aa');
insert into a values(2,'bb');

create table B
(
  id   NUMBER not null,
  name VARCHAR2(10) not null
)

insert into b values(1,'aa');
insert into b values(2,'bb');

然后执行一个not in 语句，并且看看这个SELECT 的执行计划：

select * from a where (id,name) not in 
(select id,name from b where a.id=b.id and a.name=b.name)

执行计划如下：

SELECT STATEMENT, GOAL = ALL_ROWS			7	2	80	6	
 HASH JOIN ANTI			7	2	80	6	"A"."ID"="B"."ID" AND "A"."NAME"="B"."NAME"
  TABLE ACCESS FULL	JAC	A	3	2	40	3	
  TABLE ACCESS FULL	JAC	B	3	2	40	3

清楚看到在启用了not null约束的表中，oracle选择了hash join。

然后将a,b 两表 id字段的not null 约束去掉：

sql>alter table a modify id null;
sql>alter table b modify id null;

再看看同一个查询的执行计划：

SELECT STATEMENT, GOAL = ALL_ROWS			6	1	20	6	
 FILTER							
  TABLE ACCESS FULL	JAC	A	3	2	40	3	
  TABLE ACCESS FULL	JAC	B	3	1	20	3

可以看到的是，oracle这次执行的是filter，类似于一种优化的nest loop。

经过这一次实验证明对于not null 约束条件的有无，决定了oracle对待not in查询的态度。当有not null约束时，oracle喜欢用hash join，相反，当字段无not null 约束时，oracle貌似不能使用hash join 而偏向于选择filter。注:如果hash join只能用于等值连接，那么由于 null 值的等值判断返回的是unknown，因此hash join的确是不能用在有null值的场景中。

另外，not exists 无论字段是否有not null 约束，oracle都是使用 hash join.