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

最近在书上看到一个说法，字段有无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.