深入了解Oracle中的NULL：特性、索引和查询性能问题

我们彻底分析了 Oracle DBMS 中与 NULL 相关的所有微妙之处，以及使用 NULL 索引和查询性能的问题。

关键点

特殊值 NULL 意味着没有数据，说明该值未知。默认情况下，任何类型的列和变量都可以采用此值，除非它们有约束 NOT NULL。此外，还会 DBMS 自动 NOT NULL 向表主键中包含的列添加约束。

的主要特征 NULL 是它不等于任何东西，甚至不等于另一个 NULL。您不能使用任何运算符将任何值与其进行比较：=、、like … 即使表达式也 NULL != NULL 不会为真，因为无法唯一地将一个未知数与另一个未知数进行比较。顺便说一句，这个表达式也不会为假，因为 Oracle 在计算条件时并不局限于 andTRUE 状态 FALSE。由于存在不确定性因素 NULL，因此还存在一种状态—— UNKNOWN。

因此，Oracle 不是使用二值逻辑而是使用三值逻辑进行操作。这个特征是祖父 Codd 在他的关系理论中奠定的，而 Oracle 作为关系理论 DBMS，完全遵循他的戒律。为了不去思考那些奇怪的查询结果，开发人员需要知道三值逻辑的真值表。

为了方便起见，我们将创建一个打印布尔参数状态的过程：

procedure testBool(p_bool in boolean) is
begin
	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);  -- UNKNOWN
exec testBool(null != null);  -- UNKNOWN
exec testBool(null  = 'a');  -- UNKNOWN
exec testBool(null != 'a');  -- UNKNOWN

比较 NULL

有特殊的运算符 IS NULL 和 IS NOT NULL，可以与 进行比较 NULLs。IS NULL 如果操作数为则返回 true，NULL否则返回 false。

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

相应地，IS NOT NULL执行相反的操作：如果操作数的值为 则返回 true，non-NULL如果为 则返回 false NULL：

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

此外，有关与缺失值进行比较的规则还有一些例外情况。第一个是 DECODE 函数，它认为两个 NULLs 彼此等价。其次，它们是复合索引：如果两个键包含空字段，但它们的所有非空字段都相等，则 Oracle 认为这两个键是等效的。

DECODE 违背系统：

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

布尔运算和 NULL

通常，UNKNOWN 状态的处理方式与 相同 FALSE。例如，如果您从表中选择行并且子句 x = NULL 中的条件 WHERE 计算结果为 UNKNOWN，那么您将不会获得任何行。但是，有一个区别：如果表达式 NOT(FALSE)返回 true，则 NOT(UNKNOWN)返回 UNKNOWN。逻辑运算符 AND 和 OR 在处理未知状态时也有自己的特点。具体见下面的例子。

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

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

未知数的否定给出未知数：

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

OR 操作员：

exec testBool(null or true);   -- TRUE    
AND 操作员：
exec testBool(null and true);  -- UNKNOWN
exec testBool(null and false);  -- FALSE   
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)返回了 status UNKNOWN。为了包含 NULLs 在查询结果中，您必须显式指定它：
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 让我们手动计算 for case 应用的过滤器 A =3：

由于三值逻辑的特殊性，一点 NOT IN 也不友好 NULLs：一 NULL 进入选择条件，就不等待数据。
NULL 和空字符串
这里 Oracle 偏离了标准并声明了和 空字符串 ANSI SQL 的等价性。NULL 这也许是最具争议性的功能之一，它时不时地引发多页讨论，并过渡到个性和其他棘手争议的不可或缺的属性。从文档来看，Oracle 本身并不介意改变这种情况（它说即使现在，空字符串也被视为 NULL，这可能在未来的版本中改变），但今天已经为这个 DBMS 编写了如此大量的代码，采取什么措施来改变系统的行为几乎不现实。而且，至少从 DBMS 的第七个版本（1992-1996）开始，他们就开始讨论这个问题，现在第十二个版本已经在路上了。
NULL 和空字符串是等价的：
exec testBool('' is null);  -- TRUE
如果按照经典的戒律追根溯源，那么空字符串 和 等价的原因就可以在 varchar 的存储格式和内部数据块 NULL 中找到。NULLsOracle 将表行存储在由标题和数据列组成的结构中。每列由两个字段表示：列中数据的长度（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 存储空字符串的方法，实现后一个选项将会出现问题。从语义的角度来看，这个说法是正确的，但我一直有一个问题没有得到完整的答案：经理如何在УphoneФ字段中输入空字符串，以及他如何进一步区分它来自 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);   -- NULL
end;
为了避免出现问题，最好从文档中学习规则：空字符串和 NULLOracle 中无法区分。
NULL 数学 - 计算方法
select decode(null + 10,  null, 'UNKNOWN', 'KNOWN') a from dual; -- UNKNOWN
select decode(null * 10,  null, 'UNKNOWN', 'KNOWN') a from dual; -- UNKNOWN
select decode(abs(null),  null, 'UNKNOWN', 'KNOWN') a from dual; -- UNKNOWN
select decode(sign(null), null, 'UNKNOWN', 'KNOWN') a from dual; -- UNKNOWN
连接的情况有所不同：您可以添加 NULL 到字符串，但不会改变它。这就是双重标准政策。
select null ||'AA'|| null ||'BB'|| null from dual; -- AABB
NULL 和聚合函数
几乎所有聚合函数，除了 COUNT（即使如此，也不总是），在计算过程中都会忽略空值。如果他们不这样做，那么第一个 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(*)or COUNT（常量），将计算空值。但是，如果将其用作 COUNT(表达式)，则将忽略 null 值。
有一个常数：
select count(*)    from agg; -- 4
select count(1+1)  from agg; -- 4
select count(user) from agg; -- 4
用表达式：
select count(n)      from agg; -- 3
select count(id)     from agg; -- 4
select count(abs(n)) from agg; -- 3
另外，您应该小心使用诸如 AVG. 因为它将忽略空值，所以字段 N 的结果将为(1+3+2)/3, not (1+3+2)/4。也许你不需要这样的平均计算。为了解决此类问题，有一个标准解决方案 - 使用以下 NVL 函数：
select avg(n)        from agg; -- (1 + 3 + 2) / 3 = 2
select avg(nvl(n,0)) from agg; -- (1 + 3 + 0 + 2) / 4 = 1.5
如果聚合函数 UNKNOWN 应用于空数据集或仅包含 NULLs. 例外的是设计用于计算行数的 REGR_COUNTand（表达式）函数。COUNT 在上面列出的情况下，它们将返回零。
仅包含 NULL 的数据集：
select sum(n)          from agg where n is null; -- UNKNOWN
select avg(n)          from agg where n is null; -- UNKNOWN
select regr_count(n,n) from agg where n is null; -- 0
select count(n)        from agg where n is null; -- 0
空数据集：
select sum(n)          from agg where 1 = 0; -- UNKNOWN
select avg(n)          from agg where 1 = 0; -- UNKNOWN
select regr_count(n,n) from agg where 1 = 0; -- 0
select count(n)        from agg where 1 = 0; -- 0
NULL 在索引中
创建索引时，Oracle 在索引结构中包含包含 NULL 索引列中的值的所有行的条目。这样的记录称为 NULL 记录。这使您可以快速识别相应列包含 的行，这在使用或条件 NULL 执行查询时非常有用。NULLnon-NULL


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


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


函数索引 andNULLs：函数索引是基于表列上的表达式或函数构建的。如果函数允许 NULL 参数，则索引将包含 NULL 函数参数的条目。这在优化使用可为空函数的查询时非常有用。


不良做法


NULL 对基数较低的列建立索引：在大多数值所在的列上创建索引 NULL 可能会导致索引使用率不佳和查询性能不佳。这是因为低 NULL 基数的索引会占用数据库中的大量空间，并且使用此类索引的查询可能比全表扫描慢。


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


NULL 将索引与 IS NOT NULL 运算符一起使用：如果查询包含带有 IS NOT 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索引会影响此类操作的性能，因此在设计和优化查询时考虑它们非常重要。


遵循这些良好实践将有效地 NULLs 在 Oracle 中使用索引，提高查询性能，并减少对数据库的影响。明智地使用 NULL 索引将帮助您充分利用索引并提高数据库效率。
文章来源地址 https://www.toymoban.com/diary/share/375.html
到此这篇关于深入了解 Oracle 中的 NULL：特性、索引和查询性能问题的文章就介绍到这了, 更多相关内容可以在右上角搜索或继续浏览下面的相关文章，希望大家以后多多支持 TOY 模板网！