我们彻底分析了 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 <- !!!!! exec testBool( null or false ); -- UNKNOWN exec testBool( null or null ); -- UNKNOWN
AND操作员：
exec testBool( null and true ); -- UNKNOWN exec 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)返回了 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
文章来源地址https://www.toymoban.com/diary/share/375.html
到此这篇关于深入了解Oracle中的NULL：特性、索引和查询性能问题的文章就介绍到这了,更多相关内容可以在右上角搜索或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
