深度分析Oracle中的NULL

【squids.cn】 全网zui低价RDS，免费的迁移工具DBMotion、数据库备份工具DBTwin、SQL开发工具等

关键点

特殊值NULL意味着没有数据，它声明了该值是未知的事实。默认情况下，任何类型的列和变量都可以取这个值，除非它们有一个NOT NULL约束。此外，数据库管理系统会自动向包含在表的主键中的列添加NOT NULL约束。

NULL的主要特点是它不等于任何东西，甚至不等于另一个NULL。你不能使用任何操作符：=, <, >, like...与它比较任何值。即使表达式NULL != NULL也不会为真，因为不能唯一地将一个未知数与另一个未知数进行比较。顺便说一句，这个表达式也不会是假的，因为在计算条件时，Oracle不仅限于TRUE和FALSE状态。由于存在NULL这种不确定性元素的形式，还有一个状态 —— UNKNOWN。

因此，Oracle不是使用双值逻辑，而是使用三值逻辑操作。这个特点是由Codd祖父在他的关系理论中提出的，作为一个关系型数据库管理系统，Oracle完全遵循他的教诲。为了不对查询的“奇怪”结果进行思考，开发者需要知道三值逻辑的真值表。

为了方便，我们将制作一个输出布尔参数状态的程序：

procedure testBool( p_bool in boolean ) isbegin  if p_bool = true then     dbms_output.put_line('TRUE');  elsif p_bool = false then    dbms_output.put_line('FALSE');  else     dbms_output.put_line('UNKNOWN');  end if;     end;

熟悉的比较操作符对于NULL是让步的：

exec testBool( null  = null );  -- UNKNOWNexec testBool( null != null );  -- UNKNOWNexec testBool( null  = 'a'  );  -- UNKNOWNexec testBool( null != 'a'  );  -- UNKNOWN

与NULL的比较 

有特殊的操作符，IS NULL 和 IS NOT NULL，它们允许与NULL进行比较。如果操作数是NULL，IS NULL会返回真；如果不是，它会返回假。

select case when null is null then 'YES' else 'NO' end from dual; -- YESselect case when 'a'  is null then 'YES' else 'NO' end from dual; -- NO

相应地，IS NOT NULL做相反的操作：如果操作数的值非NULL，它将返回真；如果它是NULL，它将返回假：

select case when 'a'  is NOT null then 'YES' else 'NO' end from dual; -- YESselect case when null is NOT null then 'YES' else 'NO' end from dual; -- NO

此外，关于与缺失值的比较还有几个例外。第一个是DECODE函数，它认为两个NULL是等价的。其次是复合索引：如果两个键包含空字段，但它们所有的非空字段都相等，那么Oracle会认为这两个键是等价的。

DECODE违反了这个系统：

select decode( null  , 1, 'ONE'  , null, 'EMPTY' -- это условие будет истинным   , 'DEFAULT'    )from dual;

布尔操作和NULL 

通常，UNKNOWN状态与FALSE处理方式相同。例如，如果从表中选择行，WHERE子句中的x = NULL条件评估为UNKNOWN，那么你不会得到任何行。但是，有一个区别：如果表达式NOT(FALSE)返回真，则NOT(UNKNOWN)返回UNKNOWN。逻辑操作符AND和OR在处理未知状态时也有它们自己的特点。在下面的示例中有具体说明。

在大多数情况下，未知的结果被视为FALSE：

select 1 from dual where dummy = null; -- query will not return result

未知的否定给出未知：

exec testBool( not(null  = null) ); -- UNKNOWNexec testBool( not(null != null) ); -- UNKNOWNexec testBool( not(null  = 'a')  ); -- UNKNOWNexec testBool( not(null != 'a')  ); -- UNKNOWN

或运算符：

exec testBool( null or true  );   -- TRUE    <- !!!!!exec testBool( null or false );   -- UNKNOWNexec testBool( null or null  );   -- UNKNOWN

与运算符：

exec testBool( null and true  );  -- UNKNOWNexec testBool( null and false );  -- FALSE   <- !!!!!exec testBool( null and null  );  -- UNKNOWN

IN 和 NOT IN 运算符 

我们从一些初步的步骤开始。为了测试，让我们创建一个表T，它有一个数字列A和四行：1、2、3和NULL。

create table t as select column_value a from table(sys.odcinumberlist(1,2,3,null));

启用请求追踪（您必须拥有PLUSTRACE角色才能执行此操作）。

在跟踪的列表中，只留下过滤部分，以显示请求中指定的条件是如何展开的。

set autotrace on

初步工作结束了。现在让我们使用运算符。让我们试着选择所有包含在集合(1, 2, NULL)中的记录：

select * from t where a in ( 1, 2, null ); -- will return [1, 2]​-- Predicate Information: --   filter("A"=1 OR "A"=2 OR "A"=TO_NUMBER(NULL))

如您所见，带有NULL的行没有被选中。这是因为谓词"A"=TO_NUMBER(NULL)的评估返回了UNKNOWN状态。为了在查询结果中包含NULL，您必须明确指定它：

select * from t where a in ( 1, 2 ) or a is null; -- will return [1, 2, NULL]​-- Predicate Information: --    filter("A" IS NULL OR "A"=1 OR "A"=2)

现在我们试试NOT IN：

select * from t where a not in ( 1, 2, null ); -- no rows selected​-- Predicate Information:--   filter("A"<>1 AND "A"<>2 AND "A"<>TO_NUMBER(NULL))

一个结果都没有！让我们看看为什么三元组没有包含在查询结果中。让我们手动计算DBMS为A=3情况应用的过滤器：

由于三值逻辑的特点，NOT IN 对NULL一点也不友好：只要NULL进入选择条件，就不要等待数据。

NULL 和空字符串 

在这里，Oracle偏离了ANSI SQL标准，并声明NULL和空字符串是等价的。这也许是最具争议的特点之一，时不时地会引发多页的讨论，转向个性化的争辩和其他艰难争论的必要属性。从文档来看，Oracle本身似乎不介意改变这种情况（它说即使现在，空字符串被视为NULL，这在未来版本中可能会改变），但今天为这个DBMS写了如此巨大数量的代码，所以采取行动并改变系统的行为几乎是不现实的。更重要的是，至少从DBMS的第七个版本（1992-1996）开始，他们就开始谈论这个问题，现在第十二个版本即将到来。

NULL 和空字符串是等价的：

exec testBool( '' is null );  -- TRUE

如果你遵循经典的教诲，查看根源，那么空字符串与NULL的等价性的原因可以在varchar和NULL在数据块内的存储格式中找到。Oracle在一个由标题后跟数据列组成的结构中存储表行。每个列都由两个字段表示：列中的数据长度（1或3字节）和实际的数据本身。如果varchar2的长度为零，那么在数据字段中没有什么可写的，它不占用一个字节，长度字段中写入特殊值0xFF，表示没有数据。NULL以完全相同的方式表示：没有数据字段，长度字段中写入0xFF。Oracle的开发者当然可以区分这两个状态，但这就是它们从古代开始的方式。

对我个人来说，空字符串和NULL的等价性似乎相当自然和合乎逻辑。"空行"这个名字本身就意味着没有意义，空虚，甜甜圈洞。NULL基本上意味着同样的事情。但这里有一个不愉快的后果：对于空字符串，你可以肯定地说其长度等于零，但NULL的长度则根本没有定义。因此，length('')表达式将为您返回NULL，而不是您明显期望的零。另一个问题：您不能对空字符串进行比较。表达式val = ''会返回状态UNKNOWN，因为它实际上等同于val = NULL。

空字符串的长度是未定义的：

select length('') from dual; -- NULL

与空字符串的比较是不可能的：

exec test_bool( 'a' != '' ); -- UNKNOWN

Oracle的方法的批评者认为，空字符串并不一定意味着未知。例如，销售经理填写一张客户卡。他可能会注明他的联系电话（555-123456），可能会表示他的联系方式是未知的（NULL），或者可能会表示没有联系电话（空字符串）。使用Oracle存储空字符串的方法，实现后者选项将会是个问题。从语义的角度看，这个论点是正确的，但我总是对此有一个问题，我还没有得到一个完整的答案：经理如何在“电话”字段中输入一个空字符串，并且他将如何进一步区分它与NULL之间的差异？当然，有一些方法可以解决，但仍然存在疑问。

实际上，如果我们谈论PL/SQL，在其引擎的深处，空字符串与NULL是不同的。可以证明这一点的一个方法是，关联集合允许您在索引''（一个空字符串）处存储一个元素，但不允许您在索引NULL处存储一个元素。

declare  procedure empty_or_null( p_val varchar2 )   is    type tt is table of varchar2(1) index by varchar2(10);    t tt;  begin    if p_val is not null then      dbms_output.put_line('not null');    else      -- trying to create an element with index p_val      t(p_val) := 'x';      -- happened!      dbms_output.put_line('empty string');    end if;  exception    -- it was not possible to create an element with index p_val    when others then dbms_output.put_line('NULL');  end;begin empty_or_null( 'qwe' );  -- not null empty_or_null( '' );     -- empty string empty_or_null( NULL );   -- NULLend;

为了避免问题，最好从文档中学习这条规则：在Oracle中，空字符串和NULL是无法区分的。

NULL的数学计算 

select decode( null + 10,  null, 'UNKNOWN', 'KNOWN') a from dual; -- UNKNOWNselect decode( null * 10,  null, 'UNKNOWN', 'KNOWN') a from dual; -- UNKNOWNselect decode( abs(null),  null, 'UNKNOWN', 'KNOWN') a from dual; -- UNKNOWNselect decode( sign(null), null, 'UNKNOWN', 'KNOWN') a from dual; -- UNKNOWN

但与连接操作不同：你可以将NULL添加到一个字符串中，并且它不会改变它。这就是双重标准的策略。

select null ||'AA'|| null ||'BB'|| null from dual; -- AABB

NULL与聚合函数 几乎所有的聚合函数，除了COUNT（有时也不是），在计算过程中都会忽略null值。如果它们没有这样做，那么第一个遇到的NULL就会导致函数结果变为未知值。以SUM函数为例，它需要对序列（1,3,null,2）求和。如果考虑空值，我们将得到以下操作序列：

1 + 3 = 4；4 + null = null；null + 2 = null。

当计算聚合值时，你可能不会满意这样的计算，因为你可能并不希望得到这样的结果。

数据表格。下面多次使用：

create table agg( id int, n int );insert into agg values( 1, 1 );insert into agg values( 2, 3 );insert into agg values( 3, null );insert into agg values( 4, 2 );commit;

聚合函数忽略空值：

select sum(n) from agg; -- 6

COUNT行计数函数，如果使用为COUNT(*)或COUNT(constant)，则会计算null值。但是，如果它被用作COUNT(expression)，则null值将被忽略。

使用常量：

select count(*)    from agg; -- 4select count(1+1)  from agg; -- 4select count(user) from agg; -- 4

使用表达式：

select count(n)      from agg; -- 3select count(id)     from agg; -- 4select count(abs(n)) from agg; -- 3

此外，使用如AVG这样的函数时，应该小心。因为它会忽略null值，所以N字段的结果是(1+3+2)/3，而不是(1+3+2)/4。也许你不需要这样的平均值计算。为了解决这样的问题，有一个标准解决方案 - 使用NVL函数：

select avg(n)        from agg; -- (1 + 3 + 2) / 3 = 2select avg(nvl(n,0)) from agg; -- (1 + 3 + 0 + 2) / 4 = 1.5

如果聚合函数应用于一个空的数据集或者它只由NULLs组成，那么它们将返回UNKNOWN。例外是REGR_COUNT和COUNT(expression)函数，这些函数设计用于计算行数。在上述情况下，它们将返回零。

只有NULLs的数据集：

select sum(n)          from agg where n is null; -- UNKNOWNselect avg(n)          from agg where n is null; -- UNKNOWNselect regr_count(n,n) from agg where n is null; -- 0select count(n)        from agg where n is null; -- 0

空的数据集：

select sum(n)          from agg where 1 = 0; -- UNKNOWNselect avg(n)          from agg where 1 = 0; -- UNKNOWNselect regr_count(n,n) from agg where 1 = 0; -- 0select count(n)        from agg where 1 = 0; -- 0

在索引中的NULL 

当创建索引时，Oracle在索引结构中为包含NULL值的索引列的所有行包含条目。这样的记录被称为NULL记录。这使您能够快速识别相应列包含NULL的行，这在执行带有NULL或非NULL条件的查询时可能很有用。

• 在常规索引中使用NULL值：常规索引包括对表行的引用，指示索引列的值和这些行的相应ROWIDs。对于具有NULL值的行，索引存储一个特殊的NULL标记，以指示索引列中存在NULL。这允许Oracle快速找到索引列中的NULL行。 

• 在复合索引中使用NULL值：在索引多个列的复合索引中，每个列都有自己的索引结构。因此，对于包含NULL列的复合索引，每个包含NULL的列都会有一个NULL标记。 

• 函数索引和NULLs：函数索引基于表列上的表达式或函数构建。如果函数允许NULL参数，则索引将包括NULL函数参数的条目。这在优化使用可为空函数的查询时可能很有用。 

不良做法 

• 对低NULL基数的列进行索引：在大多数值为NULL的列上创建索引可能导致次优的索引使用和查询性能不佳。这是因为低NULL基数的索引会在数据库中占用大量空间，而带有这种索引的查询可能比全表扫描还要慢。 

• 对具有NULL的非选择性列进行索引：非选择性列是具有少量唯一值或许多重复NULL值的列。在此类列上创建索引可能并不实用，因为此类索引可能无法显著提高查询性能，并且需要更多资源进行维护。 

• 使用IS NOT NULL操作符与NULL索引：如果查询包含带有IS NOT NULL操作符的条件，则查询优化器不会使用NULL索引。因此，在这样的查询中使用NULL索引将是无用的，并浪费了创建和维护不必要索引的资源。 

• 对可能包含NULL值的大文本列进行索引：在可能包含NULL值的大文本列上创建索引可能会有不利之处，因为必须在索引中存储大量数据。对此类列进行索引可能会显著增加索引的大小并降低查询性能。 

• 过度使用带有NULL的函数索引：函数索引可用于优化允许null参数的函数的查询。但是，过度使用NULL函数索引可能导致不需要的索引大小和性能降级。 

• 带有NULL的无关和未使用的索引：过时和未使用的NULL索引仍然保留在数据库中，消耗空间，并需要在数据更改时进行更新。应定期解析并删除这些索引，以减少系统负载并优化性能。 

重要的是要记住，使用NULL在索引中可以是有用的，但并不总是这样。在创建带有NULL的索引时，您应该注意列中NULL值的基数及其在查询中的实际使用情况。这将有助于避免不必要的索引并提高数据库性能。

良好做法 

• 对高NULL基数的列进行索引：在高NULL基数的列上创建索引可能是有益的，因为索引允许您快速识别具有NULL值的行。当查询经常在某一列中使用null或非null条件时，这尤其有用。 

• 对常用于查询的列进行索引：在经常用于查询的列上创建索引可以大大提高查询性能。索引可以帮助加速数据检索并减少查询执行时间。 

• 使用带有NULL的函数索引：函数索引可用于优化允许null参数的函数的查询。此类索引可以提高使用带有NULL参数的函数的查询的性能。 

• 与IS NULL结合使用带有NULL的索引：使用IS NULL操作符查找具有NULL值的行时，NULL索引可能非常有用。此类索引允许您快速找到对应列中的所有NULL行。 

使用NULL索引进行性能分析 

在创建带有NULL的索引时，建议您分析查询性能并将其与不带索引的性能进行比较。这

将帮助您确定哪些NULL索引实际上提高了查询性能，并在您的特定情况下是合理的。

• 周期性索引维护：与普通索引一样，NULL索引需要周期性维护。定期更新索引统计信息将帮助查询优化器正确评估查询执行计划并避免不必要的操作。

• 移除未使用的NULL索引：应定期解析并删除未使用的NULL索引，以减少系统负载并优化数据库性能。

• 对更新和插入进行控制：使用NULL索引时，您需要控制更新和插入操作。NULL索引可能会影响这些操作的性能，因此在设计和优化查询时很重要要考虑到它们。

遵循这些良好实践将有效地在Oracle索引中使用NULL，提高查询性能并减少对数据库的影响。明智地使用NULL索引将帮助您充分利用索引并提高数据库效率。

作者：Andrei Rogalenko

更多内容请关注公号【云原生数据库】

squids.cn，云数据库RDS，迁移工具DBMotion，云备份DBTwin等数据库生态工具。