建站制作企业,wordpress 另类加速,美的网站建设规划书,腾讯微信小程序官网【squids.cn】 全网zui低价RDS#xff0c;免费的迁移工具DBMotion、数据库备份工具DBTwin、SQL开发工具等
关键点
特殊值NULL意味着没有数据#xff0c;它声明了该值是未知的事实。默认情况下#xff0c;任何类型的列和变量都可以取这个值#xff0c;除非它们有一个NOT N…【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进行比较。如果操作数是NULLIS 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(A1 OR A2 OR ATO_NUMBER(NULL))
如您所见带有NULL的行没有被选中。这是因为谓词ATO_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 A1 OR A2)
现在我们试试NOT IN
select * from t where a not in ( 1, 2, null ); -- no rows selected-- Predicate Information:-- filter(A1 AND A2 AND ATO_NUMBER(NULL))
一个结果都没有让我们看看为什么三元组没有包含在查询结果中。让我们手动计算DBMS为A3情况应用的过滤器 由于三值逻辑的特点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 44 null nullnull 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(11) 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字段的结果是(132)/3而不是(132)/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等数据库生态工具。
