首页 > oracle知识 > oracle_SQL >

NULL判断对SQL的影响

作者: 初见博客 分类: oracle_SQL 发布时间: 2021-07-09 10:33

看到一条SQL,很具迷惑性,原始语句包含了业务属性,因此使用模拟的操作来复现这个问题。

创建两张测试表,要注意的是,object_id字段设置了NOT NULL,

SQL> create table a as select * from dba_objects;Table created.
SQL> update a set object_id = 0 where object_id is null;1 row updated.
SQL> commit;Commit complete.
SQL> create table b as select * from a ;Table created.
SQL> alter table a modify object_id not null;Table altered.

原始的SQL,如下所示,可能有经验的朋友一下就看出来了问题,Oracle中判断字段是否为空应该使用is null或者is not null,使用任何其他的比较运算符,返回的都是false,

SQL> select /*+ test1 */ a.object_id, b.object_name      from a, b      where a.object_id = b.object_id and a.object_id = null;no rows selected

他的执行计划,用的是排序合并连接,如果按照预估进行计算,从E-Rows能看出这两张表合并排序的预计行数是5330M行,应该不可能很快跑出来的,但实际上SQL很快就返回了,结果集是空,这点从A-Rows是0就可以得到证明。很重要的信息,就是在第一步,谓词信息显示filter(NULL IS NOT NULL AND NULL IS NOT NULL),明显这是假命题,他的作用,其实就是告诉Oracle的优化器不用计算成本了(可参考《Oracle优化器的“短路”》),

640

我们再进一步,看下这条SQL的10053,a.object_id=null被转换为了a.object_id=to_number(null) and b.object_id=to_number(null),这里用到的是谓词的传递性(这是为什么filter中有两个NULL IS NOT NULL),Oracle没将=null看作是对空值的判断,而将他作为一个普通的字符串处理的,由于字段object_id是number类型的,因此隐式转换to_number(null),

Final query after transformations:******* UNPARSED QUERY IS *******SELECT "A"."OBJECT_ID" "OBJECT_ID","B"."OBJECT_NAME" "OBJECT_NAME" FROM "BISAL"."A" "A","BISAL"."B" "B" WHERE "A"."OBJECT_ID"="B"."OBJECT_ID" AND "A"."OBJECT_ID"=TO_NUMBER(NULLAND "B"."OBJECT_ID"=TO_NUMBER(NULL)

to_number(null)是什么?我们看下,返回的就是个NULL,因此用=和他进行比较的结果就是“未知”的,

SQL> select to_number(null), dump(to_number(null)) from dual;TO_NUMBER(NULL) DUMP--------------- ----                NULL

我想说的是,之所以这条语句,很快返回0条,原因和《Oracle优化器的“短路”》是不同的,这里用的a.object_id = null,相当于是个错误的条件,用如下的示例,可以说明,我们使用is null检索object_id是空的记录会返回1条,但是用=null检索返回的就是0条,因为这个条件本身就是错的,自然是空,

SQL> select count(*from dba_objects where object_id is null;  COUNT(*)----------   1
SQL> select count(*from dba_objects where object_id = null;  COUNT(*)----------   0

如果用的is null判断,

SQL> select a.object_id, b.object_name from a, b where a.object_id = b.object_id and a.object_id is null;no rows selected

如下执行计划,显示用的两表HASH JOIN,同样地,谓词条件出现了NULL IS NOT NULL,Oracle不会真正做A和B的全表扫描,避免了资源消耗,

640-1

10053显示,没做任何转换,is null就是个特殊条件,

Final query after transformations:******* UNPARSED QUERY IS *******SELECT "A"."OBJECT_ID" "OBJECT_ID","B"."OBJECT_NAME" "OBJECT_NAME" FROM "BISAL"."A" "A","BISAL"."B" "B" WHERE "A"."OBJECT_ID"="B"."OBJECT_ID" AND "A"."OBJECT_ID" IS NULL

一方面说明Oracle的优化器很智能,能对这种肯定返回空的语句,施加特殊的条件,避免无用功,另一方面,我们在日常开发过程中,应该遵从一些规范避免出现=null这种判断的情况。

网上一些对NULL的描述说明,仅供参考,

1. Oracle认为NULL最大,因此排序时比其他数据都大。

2. nulls first:将NULL排在最前面。

select * from mytb order by mycol nulls first

3. null last:将NULL排在最后面。

select * from mytb order by mycol nulls last

4. 等价于没有任何值、是未知数。
5. NULL与0、空字符串、空格都不同。
6. 对空值做加、减、乘、除等运算操作,结果仍为空。
7. NULL的处理使用NVL函数或者NVL2。
8. 比较时使用关键字用“is null”和“is not null”,通过其他方式和任何值(包括NULL)的比较结果都是空。

9. 空值不能被索引,所以查询时有些符合条件的数据可能查不出来,count(*)中,用NVL(列名,0)处理后再查。

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注