题记
随着科技的飞速发展和软硬件技术的更新换代，数据库领域也在不断发生着变化，例如传统数据库与新技术的结合、数据库的云化等。改变是为了更好地生存发展，Oracle产品本身发生了很大变化，这在一定程度上影响了Oracle数据库运维者的工作模式。笔者将在本章阐释近年来Oracle在技术层面和环境的变化，以及如何才能成为一名优秀的DBA。
8.1 对数据库开发和运维的认识
笔者是从数据库开发做起，经历了开发DBA的阶段，最终成为一个管理DBA的。
数据库开发主要涉及建模、表的逻辑和物理结构设计、PL/SQL代码编写、性能优化等方面。而管理和维护DBA的任务包括逻辑备份、物理备份、恢复、迁移和升级等。虽然从工作范围上，二者可以明确区分，但是很多时候很难界定一个工作到底属于开发范畴还是管理范围。如果想要数据库的后期维护成本低，前期的设计非常重要。也就是说，如果DBA在设计物理逻辑结构时多考虑后期维护工作，就可以把很多复杂且烦琐的维护工作消除在设计阶段.Oracle目前有很大的市场占有率（根据IDC的分析报告，2015年中国市场Oracle占有率高达56%），只要是具有一定数据规模的行业，都会存在Oracle的身影。Oracle数据库的优势主要体现在大数据量OLTP环境中，Oracle特有的锁机制和多版本读一致性，最大限度提升了系统所能承载的并发用户数。而对于小数据量的情况或OLAP环境中，Oracle并没有绝对的优势。
对于Oracle数据库的发展方向，笔者认为Oracle数据库会越来越智能、所集成的功能会越来越强大，而数据库和存储的一体化（Exadata）很可能是今后几年Oracle发展的主要方向，并且这个方向会持续扩展到云上。
8.2 行业发展给DBA带来的挑战
“DBA行业将要逐渐消亡”这一说法显然是杞人忧天。Oracle的性能很稳定，而且随着新版本新功能的不断增加，原本很多需要人工设置的工作都可以自动完成了。例如创建一个表时，在数据字典管理的表空间中要考虑到非常多的表存储参数，PCTREEE、PCTUSED、INITRANS、MAXTRANS、INITIAL、NEXT、MINEXTENTS、MAXEXTENTS、PCTINCREASE、BUFFER_POOL，所有这些参数都要仔细规划。随着Oracle越来越智能化以及本地管理表空间ASSM的出现，绝大部分情况不需要再对表的存储参数进行额外修改，默认值的设置就能够满足绝大部分要求。这样的例子比比皆是，显然Oracle入门的门槛变低了。但门槛变低并不意味着成长为高级DBA的难度下降。事实上，Oracle在每个版本中都会引入大量的自动化功能。以广为人知的内存管理为例，9i Oracle推出了自动PGA管理，10g推出了自动SGA管理，11g推出了自动内存管理。但是在客户的关键应用上，笔者并不建议客户使用11g的新特性自动内存管理，甚至在一些非常繁忙的OLTP数据库中，为了避免Oracle在SGA各个组件之间自动调整内存，建议客户手工配置各个内存组件的大小。
一个高水平的DBA不但要了解Oracle推出的新特性的功能，还要了解这个新特性的限制条件以及可能导致的问题。如果出现问题，要能对这个特性产生的问题进行深入分析和诊断。所以，从这个角度来看，DBA需要掌握的知识会越来越多。随着Oracle越来越自动化，初级DBA的职位可能会有一定危险。如果Oracle升级到了高版本，而DBA的知识框架却不更新，那么这个DBA很可能会被淘汰。因此，一个上进的DBA要能够随着数据架构的变化和行业的发展不断提升自己的能力，合理规划，不断进步。对于现在仍在Oracle运维岗位的人来说，既然上了Oracle这个快行道，就义无反顾地走下去吧！
8.3 个人学习经验分享
如何成为一个优秀的DBA呢?
有关Oracle技术学习的文章非常多，方法并不是最重要的，持之以恒的学习才是成功的关键。笔者的学习方法是从阅读Oracle官方文档起步，首先看Concept，然后看Administrator，最后是Backup、Performance Tunning、RAC、Data Guard、Upgrade、Utilities、Network等。研究技术没有太多的窍门可言，对笔者来说，一个十分有帮助的方法是：对Oracle技术有一定了解后，自己多做一些总结性的工作，包括做测试、写BLOG。这样可以把Oracle技术的发展梳理出来，了解了一个技术的发展过程，再去看它之后的改变和发展，就更加得心应手。
1．抬头看路
除了潜心研究外，多关注新的技术发展趋势也十分关键。低头做事、抬头看路，了解最新的技术发展和行业趋势可以避免走弯路，有助于更好地理解技术的演进过程。
大部分的技术人员都喜欢埋头研究技术，而技术大会正是一个让技术人员抬头看路的机会。所以，笔者鼓励运维者多参加各类技术会议，了解更多的业内最新动态，结识圈内更多的技术专家。
2．坚持记录，分享知识
博客在笔者的技术成长中占有很重要的地位，个人这些年也一直坚持更新博客，为大家分享了很多有价值的技术和经验。
其实笔者开始写博客时，博客已经流行一段时间了。开始没有想过要申请一个博客，直到有一天自己通过查询文档解决了一个比较复杂的问题后，才发现如果不把这个解决问题的步骤记录下来，那么过不了多长时间，很可能就会忘记一些关键的步骤，下次即使碰到同样的问题，仍然可能需要从头开始。如果将其保存在文本文件中，时间久了，查找起来就会很不方便，而当时的博客恰好可以满足需要，于是笔者的博客也就这样诞生了。
坚持做一件事情并不容易，笔者也有一段时间没有坚持更新博客，慢慢发现如果不去更新博客，自己就会变得比较懒，工作之外花在学习Oracle上的时间也会随之减少。意识到这一点后，笔者恢复了博客的更新，并给自己定下了每天更新一篇的目标，让博客成为督促自己每天学习Oracle知识、总结Oracle经验的工具。笔者建议大家多写博客，将自己有价值的技术和经验记录下来，分享给大家，这样后来的人就能够从前人的经验中学习，避免重复低级的错误。当然运维本身现在也面临着繁冗重复的问题，博客并不能解决这个问题，但无论如何，博客都是记录和分享知识的一个很好的途径。
8.4 Oracle中的NULL 剖析
经常看到很多人提出和NULL有关的问题。NULL是数据库中特有的类型，Oracle中很多容易出现的错误都和NULL有关。下面简单总结一些NULL的相关知识。
8.4.1 NULL的基础概念和由来
NULL是数据库中特有的数据类型，当一条记录的某个列为NULL，则表示这个列的值是未知的、不确定的。既然是未知的，就有无数种的可能性。因此，NULL并不是一个确定的值。这是NULL的由来，也是NULL的基础，所有和NULL相关的操作的结果都可以从NULL的概念推导出来。判断一个字段是否为NULL，应该用IS NULL或IS NOT NULL，而不能用‘=’。对NULL的判断只能定性，而不能定值。简单地说，由于NULL存在着无数的可能，因此两个NULL既不是相等的关系，也不是不相等的关系，同样不能比较两个NULL的大小，这些操作都是没有意义的，得不到一个确切的答案的。因此，对NULL的=、!=、>、<、>=、<=等操作的结果都是未知的，这些操作的结果仍然是NULL。同理，对NULL进行+、−、*、/等操作的结果也是未知的，结果也是NULL。所以通常会这样总结NULL，除了IS NULL、IS NOT NULL以外，对NULL的任何操作的结果还是NULL。
上面这句话总结得很精辟，而且很好记，所以通常人们只记得这句话，而忘了这句话是如何得到的。只要清楚NULL的真正含义，在处理NULL的时候就不会出错。
首先看一个经典的例子。
SQL> CREATE OR REPLACE PROCEDURE P1 (P_IN IN NUMBER) AS 2 BEGIN 3 IF P_IN >= 0 THEN 4 DBMS_OUTPUT.PUT_LINE('TRUE'); 5 ELSE 6 DBMS_OUTPUT.PUT_LINE('FALSE'); 7 END IF; 8 END; 9 / 过程已创建。 SQL> CREATE OR REPLACE PROCEDURE P2 (P_IN IN NUMBER) AS 2 BEGIN 3 IF P_IN < 0 THEN 4 DBMS_OUTPUT.PUT_LINE('FALSE'); 5 ELSE 6 DBMS_OUTPUT.PUT_LINE('TRUE'); 7 END IF; 8 END; 9 / 过程已创建。复制
上面两个过程是否等价？如果你输入一个大于等于0的值，第一个过程会输出TRUE，而第二个过程也会输出TRUE。同理，如果输入一个小于0的值，则两个过程都会输出FALSE。因此对于熟悉C或JAVA的开发人员来说，可能认为这两个过程是等价的，但是在数据库中，输入的值除了大于等于0和小于0之外，还有第3种可能性：NULL。
当输入为NULL时，可以看到上面两个过程不同的输出。
SQL> SET SERVEROUT ON SQL> EXEC P1(NULL) FALSE PL/SQL 过程已成功完成。 SQL> EXEC P2(NULL) TRUE PL/SQL 过程已成功完成。复制
输入为NULL时，无论是第一个过程还是第二个过程，在流程中的第一个判断语句的结果是一样的：不管是NULL >= 0还是NULL < 0结果都是未知，所以两个判断的结果都是NULL。最终，在屏幕上输出的分别是两个过程中定义的判断ELSE分支中的输出值。
8.4.2 NULL的布尔运算的特点
由于引入了NULL，在处理逻辑过程中一定要考虑NULL的情况。同样的，布尔值的处理也需要考虑NULL的情况，这使得布尔值从原来的TRUE和FALSE两个值变成了TRUE、FALSE和NULL 3个值。
下面是TRUE和FALSE两种情况进行布尔运算的结果。
AND操作如表8-1所示。
表8-1
| AND | TRUE | FALSE |
|---|---|---|
| TRUE | TRUE | FALSE |
| FALSE | FALSE | FALSE |
OR操作如表8-2所示。
表8-2
| OR | TRUE | FALSE |
|---|---|---|
| TRUE | TRUE | TRUE |
| FALSE | TRUE | FALSE |
上面是熟悉的TRUE和FALSE两个值进行布尔运算的结果，如果加上一个NULL的情况会怎样？NULL的布尔运算是否会像NULL的算术运算那样，结果都是NULL呢？下面通过一个过程来说明。
SQL> SET SERVEROUT ON SIZE 100000 SQL> DECLARE 2 TYPE T_BOOLEAN IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER; 3 V_BOOL1 T_BOOLEAN; 4 V_BOOL2 T_BOOLEAN; 5 6 PROCEDURE P(P_IN1 BOOLEAN, P_IN2 BOOLEAN, P_OPERATOR IN VARCHAR2) AS 7 V_RESULT BOOLEAN; 8 BEGIN 9 IF P_IN1 IS NULL THEN 10 DBMS_OUTPUT.PUT('NULL '); 11 ELSIF P_IN1 THEN 12 DBMS_OUTPUT.PUT('TRUE '); 13 ELSE 14 DBMS_OUTPUT.PUT('FALSE '); 15 END IF; 16 17 IF P_OPERATOR = 'AND' THEN 18 DBMS_OUTPUT.PUT('AND '); 19 V_RESULT := P_IN1 AND P_IN2; 20 ELSIF P_OPERATOR = 'OR' THEN 21 DBMS_OUTPUT.PUT('OR '); 22 V_RESULT := P_IN1 OR P_IN2; 23 ELSE 24 RAISE_APPLICATION_ERROR('-20000', 'INPUT PARAMETER P_OPERATOR ERROR'); 25 END IF; 26 27 IF P_IN2 IS NULL THEN 28 DBMS_OUTPUT.PUT('NULL'); 29 ELSIF P_IN2 THEN 30 DBMS_OUTPUT.PUT('TRUE'); 31 ELSE 32 DBMS_OUTPUT.PUT('FALSE'); 33 END IF; 34 35 IF V_RESULT IS NULL THEN 36 DBMS_OUTPUT.PUT(':NULL'); 37 ELSIF V_RESULT THEN 38 DBMS_OUTPUT.PUT(':TRUE'); 39 ELSE 40 DBMS_OUTPUT.PUT(':FALSE'); 41 END IF; 42 DBMS_OUTPUT.NEW_LINE; 43 END; 44 45 BEGIN 46 V_BOOL1(1) := TRUE; 47 V_BOOL1(2) := FALSE; 48 V_BOOL1(3) := NULL; 49 V_BOOL2 := V_BOOL1; 50 FOR I IN 1..V_BOOL1.COUNT LOOP 51 FOR J IN 1..V_BOOL2.COUNT LOOP 52 P(V_BOOL1(I), V_BOOL2(J), 'AND'); 53 P(V_BOOL1(I), V_BOOL2(J), 'OR'); 54 END LOOP; 55 END LOOP; 56 END; 57 / TRUE AND TRUE:TRUE TRUE OR TRUE:TRUE TRUE AND FALSE:FALSE TRUE OR FALSE:TRUE TRUE AND NULL:NULL TRUE OR NULL:TRUE FALSE AND TRUE:FALSE FALSE OR TRUE:TRUE FALSE AND FALSE:FALSE FALSE OR FALSE:FALSE FALSE AND NULL:FALSE FALSE OR NULL:NULL NULL AND TRUE:NULL NULL OR TRUE:TRUE NULL AND FALSE:FALSE NULL OR FALSE:NULL NULL AND NULL:NULL NULL OR NULL:NULL PL/SQL 过程已成功完成。复制
由于NULL是未知的，所以NULL AND NULL、NULL OR NULL、NULL AND TRUE和NULL OR FALSE的值都是未知的，结果仍然是NULL。为什么NULL AND FALSE和NULL OR TRUE得到了一个确定的结果呢？这需要从NULL的概念来考虑。NULL的定义是未知的，但是目前NULL的类型是布尔类型，虽然NULL的值不确定，但是NULL所在的类型确定了值的范围，因此NULL只有可能是TRUE或者FALSE中的一个。根据前面的表格，TRUE AND FALSE和FALSE AND FALSE的结果都是FALSE，也就是说，不管NULL的值是TRUE还是FALSE，它与FALSE进行AND的结果一定是FALSE。同理，TRUE OR TRUE和FALSE OR TRUE的结果都是TRUE，所以不管NULL取何值，NULL和TRUE、OR的结果都是TRUE。
AND操作图表如表8-3所示。
表8-3
| AND | TRUE | FALSE | NULL |
|---|---|---|---|
| TRUE | TRUE | FALSE | NULL |
| FALSE | FALSE | FALSE | FALSE |
| NULL | NULL | FALSE | NULL |
OR操作图表如表8-4所示。
表8-4
| OR | TRUE | FALSE | NULL |
|---|---|---|---|
| TRUE | TRUE | TRUE | TRUE |
| FALSE | TRUE | FALSE | NULL |
| NULL | TRUE | NULL | NULL |
下面来看一个例子。
SQL> SELECT * FROM TAB; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- PLAN_TABLE TABLE T TABLE T1 TABLE T2 TABLE T3 TABLE TEST TABLE TEST1 TABLE TEST_CORRUPT TABLE T_TIME TABLE 已选择9行。 SQL> SELECT * FROM TAB WHERE TNAME IN ('T', 'T1', NULL); TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- T TABLE T1 TABLE SQL> SELECT * FROM TAB WHERE TNAME NOT IN ('T', 'T1', NULL); 未选定行复制
对于IN、NOT IN与NULL的关系前面并没有说明，不过可以对其进行简单的变形。表达式TNAME IN （‘T’， ‘T1’， NULL）等价于TNAME = ‘T’ OR TNAME = ‘T1’ OR TNAME = NULL，根据前面的布尔运算结果，当查询到T或T1这两条记录时，WHERE条件相当于TRUE OR FALSE OR NULL，其结果是TRUE，因此返回了两条记录。表达式TNAME NOT IN （‘T’， ‘T1’， NULL）等价于TNAME != ‘T’ AND TNAME != ‘T1’ AND TNAME != NULL，这时WHERE条件相当于TRUE AND TRUE AND NULL或TRUE AND FALSE AND NULL，其最终结果不是NULL就是FALSE，所以查询不会返回记录。
下面讨论一下NULL的布尔值运算NOT。对于TRUE和FALSE的NOT运算很简单，NOT TRUE=FALSE，NOT FALSE=TRUE，那么如果包含NULL的情况呢？
SQL> SET SERVEROUT ON SIZE 100000 SQL> DECLARE 2 TYPE T_BOOLEAN IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER; 3 V_BOOL T_BOOLEAN; 4 5 PROCEDURE P(P_IN BOOLEAN) AS 6 V_RESULT BOOLEAN; 7 BEGIN 8 IF P_IN IS NULL THEN 9 DBMS_OUTPUT.PUT('NOT NULL'); 10 ELSIF P_IN THEN 11 DBMS_OUTPUT.PUT('NOT TRUE'); 12 ELSE 13 DBMS_OUTPUT.PUT('NOT FALSE'); 14 END IF; 15 16 V_RESULT := NOT P_IN; 17 18 IF V_RESULT IS NULL THEN 19 DBMS_OUTPUT.PUT(':NULL'); 20 ELSIF V_RESULT THEN 21 DBMS_OUTPUT.PUT(':TRUE'); 22 ELSE 23 DBMS_OUTPUT.PUT(':FALSE'); 24 END IF; 25 DBMS_OUTPUT.NEW_LINE; 26 END; 27 28 BEGIN 29 V_BOOL(1) := TRUE; 30 V_BOOL(2) := FALSE; 31 V_BOOL(3) := NULL; 32 FOR I IN 1..V_BOOL.COUNT LOOP 33 P(V_BOOL(I)); 34 END LOOP; 35 END; 36 / NOT TRUE:FALSE NOT FALSE:TRUE NOT NULL:NULL PL/SQL 过程已成功完成。复制
现在看到了一个很有趣的结果，NOT NULL的结果仍然是NULL。可能很多人对此并不理解，下面从NULL的基本概念来解释。NULL表示未知，而增加一个NOT操作后，并不能使NULL变为一个确定的值，如果NULL的值是TRUE，NOT TRUE将变为FALSE，如果值是FALSE，NOT FALSE将变为TRUE。因此即使进行了NOT操作，NULL本身的不确定性是仍然存在的，这就是最终结果仍然是NULL的原因。这里需要注意：这个NOT NULL是一个布尔操作，要和SQL中的NOT NULL约束区分开。NOT NULL约束是一个定性的描述，表示列中的数据不允许为NULL。而这里的布尔操作是在求值，要得到对NULL取非后的结果，所以仍然得到NULL，如表8-5所示。
表8-5
| NOT TRUE | NOT FALSE | NOT NULL |
|---|---|---|
| FALSE | TRUE | NULL |
8.4.3 NULL的默认数据类型
Oracle的NULL的含义是不确定，那么不确定的东西也会有确定的数据类型吗？换个说法，NULL在Oracle中的默认数据类型是什么？下面通过两个例子来说明这个问题。NULL的默认类型是字符类型，确切地说是VARCHAR2类型。我们知道一个字段不管是何种类型的，都可以插入NULL值，也就是说，NULL可以随意地转换为任意的类型。绝大部分的函数输入值为NULL，返回的结果也为NULL，这就使我们不能通过函数的返回结果判断NULL的类型。最常用来分析数据的DUMP函数，这次也失效了。
SQL> SELECT DUMP(NULL) FROM DUAL; DUMP \---- NULL复制
试图通过CREATE TABLE AS来判定NULL的类型也是不可能的。
SQL> CREATE TABLE T AS SELECT TNAME, NULL COL1 FROM TAB; CREATE TABLE T AS SELECT TNAME, NULL COL1 FROM TAB * ERROR 位于第 1 行: ORA-01723: 不允许长度为 0 的列复制
发现NULL的数据类型的过程比较偶然，下面通过一个例子来简单说明。
SQL> create table t (id number); 表已创建。 SQL> insert into t values (1); 已创建 1 行。 SQL> insert into t values (8); 已创建 1 行。 SQL> insert into t values (0); 已创建 1 行。 SQL> insert into t values (15); 已创建 1 行。 SQL> commit; 提交完成。复制
返回结果需要按照T中的ID的升序显示数据，SQL如下。
SQL> select * from t order by id; ID \---------- 0 1 8 15复制
需求还有一点额外的要求，返回结果中0值比较特殊，其他结果正常排序，但是0排在所有非0值的后面。实现的方法有很多，比如使用UNION ALL将非0值和0值分开，或者将0值转换为一个很大的数值。这两种方法都有小缺点，前者需要扫描表两次，后者无法解决ID最大值不确定的情况。因此选择在排序的时候将0转化为NULL的方法，这样利用排序时NULL最大的原理，得到了希望的结果。
SQL> select * from t order by decode(id, 0, null, id); ID \---------- 1 15 8 0复制
0确实排在了最后，但是返回结果并不正确，15居然排在了8的前面。这种结果感觉似乎是根据字符类型排序得到的。
检查排序的DECODE函数。```
SQL> select decode(id, 0, null, id) from t;
DECODE(ID,0,NULL,ID)
----------------------------------------
1
8
15
看到DECODE函数的结果，就知道问题所在了。字符类型结果在SQLPLUS显示左对齐，而数值类型是右对齐， 当前的结果说明DECODE函数的结果是字符类型。现在处理的是数值类型，为什么会得到字符类型的输出呢？在DECODE函数中，输入的4个参数，两个ID和0都是NUMBER类型，只有NULL这一个输入参数类型是不确定的，导致问题的原因就是NULL。 为了验证NULL是导致问题的原因，检查标准包中DECODE函数的定义。 下面的DECODE函数定义是从STANDARD中摘取出来的部分内容。复制
function DECODE (expr NUMBER, pat NUMBER, res NUMBER) return NUMBER;
function DECODE (expr NUMBER,
pat NUMBER,
res VARCHAR2 CHARACTER SET ANY_CS)
return VARCHAR2 CHARACTER SET res%CHARSET;
function DECODE (expr NUMBER, pat NUMBER, res DATE) return DATE;
function DECODE (expr VARCHAR2 CHARACTER SET ANY_CS,
pat VARCHAR2 CHARACTER SET expr%CHARSET,
res NUMBER) return NUMBER;
function DECODE (expr VARCHAR2 CHARACTER SET ANY_CS,
pat VARCHAR2 CHARACTER SET expr%CHARSET,
res VARCHAR2 CHARACTER SET ANY_CS)
return VARCHAR2 CHARACTER SET res%CHARSET;
function DECODE (expr VARCHAR2 CHARACTER SET ANY_CS,
pat VARCHAR2 CHARACTER SET expr%CHARSET,
res DATE) return DATE;
function DECODE (expr DATE, pat DATE, res NUMBER) return NUMBER;
function DECODE (expr DATE,
pat DATE,
res VARCHAR2 CHARACTER SET ANY_CS)
return VARCHAR2 CHARACTER SET res%CHARSET;
function DECODE (expr DATE, pat DATE, res DATE) return DATE;
观察上面的定义不难发现，虽然Oracle对DECODE函数进行了大量的重载，且DECODE函数支持各种数据类型。但是DECODE函数具有一个规律，就是DECODE函数的返回值的类型和DECODE函数的输入参数中第一个用来返回的参数的数据类型一致。这句话可能不易理解，举个简单的例子。复制
SQL> select decode(id, 1, ‘1’, 2) from t;
D
-
1
2
2
2
SQL> select decode(id, ‘1’, 1, ‘2’) from t;
DECODE(ID,‘1’,1,‘2’)
--------------------
1
2
2
2
从这两个简单的例子可以看出，DECODE的返回值的数据类型和DECODE函数中第一个表示返回的参数的数据类型一致。那么可以推断，NULL的默认数量类型是字符类型，这导致DECODE的结果变成了字符串，而查询根据字符串规则进行排序比较，因此15小于8。解决排序被转为字符类型比较的方法很多，下面举例说明。复制
SQL> select * from t order by decode(id, 1, 1, 0, null, id);
ID
----------
1
8
15
0
SQL> select * from t order by to_number(decode(id, 0, null, id));
ID
----------
1
8
15
0
SQL> select * from t order by decode(id, 0, cast(null as number), id);
ID
----------
1
8
15
0
SQL> select * from t order by decode(id, 0, to_number(null), id);
ID
----------
1
8
15
0
回到前面讨论的问题，根据DECODE类型的返回结果，可以得到一个结论：当Oracle根据NULL来判断返回类型时，Oracle给出的结果是字符串类型。 有人可能会认为这是DECODE函数的特性而已，下面还有一个比较特别的例子，同样可以说明这个问题。复制
SQL> CREATE VIEW V_NULL AS
2 SELECT NULL N
3 FROM DUAL;
视图已创建。
SQL> DESC V_NULL
名称 是否为空? 类型
N VARCHAR2
虽然建表时使用NULL会报错，但是创建视图并不会报错，而且观察视图的定义可以发现，Oracle把NULL当作VARCHAR2类型来处理。 ### 8.4.4 空字符串’ ’与NULL的关系 很多人对空字符串’’不是很清楚，这里做简单总结。 以前笔者总说空字符串’’等价于NULL，确切地说，空字符串’’是NULL的字符类型的表现格式。 证明空字符串就是NULL是很容易的。复制
SQL> SELECT 1 FROM DUAL WHERE ‘’ = ‘’;
未选定行
SQL> SELECT 1 FROM DUAL WHERE ‘’ IS NULL;
1
----------
1
SQL> SELECT DUMP(’’), DUMP(NULL) FROM DUAL;
DUMP DUMP
NULL NULL
上面3个SQL语句，任意一个都足以证明空字符串’’就是NULL。 有些人可能会说，既然’’就是NULL，为什么不能进行IS ’’的判断呢？复制
SQL> SELECT 1 FROM DUAL WHERE ‘’ IS ‘’;
SELECT 1 FROM DUAL WHERE ‘’ IS ‘’
*
第 1 行出现错误:
ORA-00908: 缺失 NULL 关键字
从上面的错误信息就可以看到答案。原因是IS NULL是Oracle的语法，在Oracle运行的时刻’’是NULL，但是现在Oracle还没有运行这条SQL，就由于语法不正确被SQL分析器挡住了。Oracle的语法并不包含IS ’’的写法，所以，这一点并不能成为’’不是NULL的理由。 为什么要说’’是NULL的字符表示形式呢？因为’’和NULL还确实不完全一样。对于NULL来说，它表示了各种数据类型的NULL值。而对于空字符串’’来说，虽然它也具有NULL的可以任意转化为其他任何数据类型的特点，但是无论是从形式上，还是从本质上，它都表现出了字符类型的特点。 下面通过一个例子来证明’’本质是字符类型的NULL。复制
SQL> CREATE OR REPLACE PACKAGE P_TEST_NULL AS
2 FUNCTION F_RETURN (P_IN IN NUMBER) RETURN VARCHAR2;
3 FUNCTION F_RETURN (P_IN IN VARCHAR2) RETURN VARCHAR2;
4 END;
5 /
程序包已创建。
SQL> CREATE OR REPLACE PACKAGE BODY P_TEST_NULL AS
2
3 FUNCTION F_RETURN (P_IN IN NUMBER) RETURN VARCHAR2 AS
4 BEGIN
5 RETURN ‘NUMBER’;
6 END;
7
8 FUNCTION F_RETURN (P_IN IN VARCHAR2) RETURN VARCHAR2 AS
9 BEGIN
10 RETURN ‘VARCHAR2’;
11 END;
12
13 END;
14 /
程序包体已创建。
SQL> SELECT P_TEST_NULL.F_RETURN(3) FROM DUAL;
P_TEST_NULL.F_RETURN(3)
------------------------------------------------------------
NUMBER
SQL> SELECT P_TEST_NULL.F_RETURN(‘3’) FROM DUAL;
P_TEST_NULL.F_RETURN(‘3’)
------------------------------------------------------------
VARCHAR2
SQL> SELECT P_TEST_NULL.F_RETURN(’’) FROM DUAL;
P_TEST_NULL.F_RETURN(’’)
------------------------------------------------------------
VARCHAR2
SQL> SELECT P_TEST_NULL.F_RETURN(NULL) FROM DUAL;
SELECT P_TEST_NULL.F_RETURN(NULL) FROM DUAL
*
第 1 行出现错误:
ORA-06553: PLS-307: 有太多的 ‘F_RETURN’ 声明与此次调用相匹配
利用重载的原理，字符类型输出VARCHAR2，数值类型输出NUMBER。输入为空字符串时，输出为VARCHAR2。从这一点上可以看出，’’实际上已经具备了数据类型。所以笔者将’’表述为空字符串是NULL的字符类型表现形式。上面根据重载的特性证明了空字符就是NULL的字符表现形式。下面简单描述字符串合并操作||的特殊性。根据NULL的定义，NULL是不确定、未知的含义，那么为什么字符类型的NULL是一个空字符呢？对于NULL的加、减、乘、除等操作的结果都是NULL，为什么字符串合并操作||，当输入字符串有一个为空时，不会得到结果NULL？复制
SQL> SELECT NULL || ‘A’, ‘B’ || NULL, NULL || NULL FROM DUAL;
NU ’ N
A B
上面两个问题需要从NULL的存储格式上解释。Oracle在存储数据时，先是存储这一列的长度，然后存储列数据本身。对于NULL，只包含一个FF，没有数据部分。简单地说，Oracle用长度FF来表示NULL。由于Oracle在处理数据存储时尽量避免0的出现，因此，认为FF表示的长度为0也是有一定道理的。或者从另一方面考虑，NULL只有一个长度，没有数据部分。对于字符串来说，不管是长度为0的字符串还是没有任何数据的字符串，代表的都是一个空字符串。空字符串就是NULL也有一定的道理。如果认为空字符串是字符形式的NULL，那么||操作的结果就不难理解了。最后需要说明的是，不要将ORACLE里面的空字符串’’与C语言中的空字符串””混淆。C语言中的空字符串并非不包含任何数据，里面包含了一个字符串结束符\0。C语言中的空字符串””对应Oracle中ASCII表中的0值，即CHR（0）。但CHR（0）是一个确定的值，显然不是NULL。复制
SQL> SELECT * FROM DUAL WHERE CHR(0) = CHR(0);
D
-
X
SQL> SELECT * FROM DUAL WHERE CHR(0) IS NULL;
未选定行
### 8.4.5 NULL和索引 NULL类型比较容易出错，索引则让NULL又一次成为问题的焦点。有一句很有名的话：索引不存储NULL值。这句话其实并不严谨。如果采用比较严谨的方式来说应为：B树索引不存储索引列全为空的记录。如果把这句话用在单列索引上，就是B树索引不存储NULL。索引分为BTREE和BITMAP两种，BTREE索引不存储NULL值，而BITMAP索引是存储NULL值的。从索引列的个数来划分，索引非为单列索引和复合索引。单列索引很简单，如果一条记录中索引字段为空，那么索引不会保存这条记录的信息。对于复合索引，由于存在着多个列，如果某一个索引列不为空，那么索引就会包含这条记录，即使索引中其他的所有列都是NULL值。复制
SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;
表已创建。
SQL> DESC T
名称 是否为空? 类型
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> CREATE INDEX IND_T_OBJECT_ID ON T (OBJECT_ID);
索引已创建。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, ‘T’, CASCADE => TRUE)
PL/SQL 过程已成功完成。
SQL> SET AUTOT ON EXP
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
50297
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 50297 | 41 (3)| 00:00:01 |
-------------------------------------------------------------------
SQL> SELECT /*+ INDEX(T IND_T_OBJECT_ID) / COUNT() FROM T;
COUNT(*)
----------
50297
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 50297 | 41 (3)| 00:00:01 |
-------------------------------------------------------------------
Oracle的优化器在确定是否使用索引时，第一标准是能否得到一个正确的结果。由于OBJECT_ID可以为空，而索引列不包含为空的记录。因此，通过索引扫描无法得到一个正确的结果，这就是SELECT COUNT（*） FROM T不会使用OBJECT_ID上的索引的原因。 对于BITMAP索引，则是另外的情况。复制
SQL> DROP INDEX IND_T_OBJECT_ID;
索引已删除。
SQL> CREATE BITMAP INDEX IND_B_T_DATA_ID ON T (DATA_OBJECT_ID);
索引已创建。
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
50297
执行计划
----------------------------------------------------------
Plan hash value: 3051411170
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | BITMAP CONVERSION COUNT | | 50297 | 2 (0)|
| 3 | BITMAP INDEX FULL SCAN | IND_B_T_DATA_ID | | |
-------------------------------------------------------------------------
SQL> SELECT COUNT(*) FROM T WHERE DATA_OBJECT_ID IS NULL;
COUNT(*)
----------
46452
执行计划
----------------------------------------------------------
Plan hash value: 2587852253
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes| Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1| 2| 2 (0)|
| 1 | SORT AGGREGATE | | 1| 2| |
| 2 | BITMAP CONVERSION COUNT | | 46452| 92904| 2 (0)|
|* 3 | BITMAP INDEX SINGLE VALUE| IND_B_T_DATA_ID| | | |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(“DATA_OBJECT_ID” IS NULL)
从上面的结果不难看出BITMAP索引中包含NULL。
下面看复合索引的情况。
SQL> DROP INDEX IND_B_T_DATA_ID;
索引已删除。
SQL> CREATE INDEX IND_T_OBJECT_DATA ON T(OBJECT_ID, DATA_OBJECT_ID);
索引已创建。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, ‘T’, METHOD_OPT => ‘FOR ALL INDEXED COLUMNS’)
PL/SQL 过程已成功完成。
SQL> SELECT OBJECT_ID, DATA_OBJECT_ID FROM T WHERE OBJECT_ID = 135;
OBJECT_ID DATA_OBJECT_ID
135
执行计划
----------------------------------------------------------
Plan hash value: 1726226519
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 1 (0)|
|* 1 | INDEX RANGE SCAN| IND_T_OBJECT_DATA | 1 | 7 | 1 (0)|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access(“OBJECT_ID”=135)
虽然结果中包含NULL值，但是Oracle并没有读取表，仅通过索引扫描返回了最终结果，这证实了复合索引中可以包含NULL值。这里说明了索引和NULL值的关系。但没有对反键索引（reverse）、逆序索引（desc）、函数索引（FBI）和CLUSTER索引进行说明。原因是这些索引并没有脱离BTREE索引和BITMAP索引的范畴。不必关心索引是否倒序或者反键，只要是BTREE索引，就不会存储全NULL记录；反之，只要是BITMAP索引就会存储NULL值。需要注意的有：函数索引，与普通索引的区别是，函数索引的真正索引列是函数的计算结果，而不是行记录中的数据。域索引，由于域索引的实现本身可能会很复杂，Oracle可能在内部是用一套表和过程来实现的，因此域索引是否存储NULL，要根据域索引的实现具体分析。 下面来看NULL与索引使用的关系。 很多人有一些错误的观点，认为指定IS NULL或者IS NOT NULL后无法使用索引，事实上很多和NULL相关的观点是RBO时代遗留下来的，已经不适用于CBO优化器了。 观点一：判断一个列IS NOT NULL不会使用索引。 这个观点解释不通，因为B树索引本身不存储键值全为NULL的记录，所以通过索引扫描得到的结果一定满足IS NOT NULL的要求。复制
SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;
表已创建。
SQL> CREATE INDEX IND_T_DATAID ON T(DATA_OBJECT_ID);
索引已创建。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, ‘T’)
PL/SQL 过程已成功完成。
SQL> SET AUTOT TRACE
SQL> SELECT COUNT(*) FROM T WHERE DATA_OBJECT_ID IS NOT NULL;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=2)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN) OF ‘IND_T_DATAID’ (NON-UNIQUE) (Cost=26 Card=2946 Bytes=5892)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
4 physical reads
0 redo size
377 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
索引的存储特性和IS NOT NULL访问本身没有冲突，因此很容易通过索引来得到相应的结果。 观点二：判断一个列IS NULL不会使用索引。 这里就不用BITMAP索引来举例了，即使是B树索引，这个观点也是不正确的。B树索引不存储键值全为空的记录，所以IS NULL操作无法使用单列索引。但复合索引可能存储一部分NULL值，所以IS NULL操作也并非不可能使用索引。复制
SQL> ALTER TABLE T MODIFY OWNER NOT NULL;
表已更改。
SQL> UPDATE T SET OBJECT_ID = NULL WHERE ROWNUM = 1;
已更新 1 行。
SQL> CREATE INDEX IND_T_OBJECT_OWNER ON T (OBJECT_ID, OWNER);
索引已创建。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, ‘T’, METHOD_OPT => ‘FOR ALL INDEXED COLUMNS SIZE 200’)
PL/SQL 过程已成功完成。
SQL> SET AUTOT TRACE
SQL> SELECT * FROM T WHERE OBJECT_ID IS NULL;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=93)
1 0 TABLE ACCESS (BY INDEX ROWID) OF ‘T’ (Cost=3 Card=1 Bytes=93)
2 1 INDEX (RANGE SCAN) OF ‘IND_T_OBJECT_OWNER’ (NON-UNIQUE) (Cost=2 Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1156 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
从上面的两个例子可以看到，Oracle的CBO不会因为SQL语句中指定了IS NOT NULL或IS NULL操作就不再使用索引。CBO选择索引只需满足结果正确和代价最小这两个条件。 ### 8.4.6 NULL的其他方面特点 NULL的一个显著特点就是两个NULL不相等。无法通过等号来判断两个NULL是否相等，利用唯一约束的特点也可以证实这一点，对于建立了唯一约束的列，Oracle允许插入多个NULL值，这是因为Oracle不认为这些NULL是相等的。复制
SQL> CREATE TABLE T (ID NUMBER, CONSTRAINT UN_T UNIQUE(ID));
表已创建。
SQL> INSERT INTO T VALUES (1);
已创建 1 行。
SQL> INSERT INTO T VALUES (1);
INSERT INTO T VALUES (1)
ERROR 位于第 1 行:
ORA-00001: 违反唯一约束条件 (YANGTK.UN_T)
SQL> INSERT INTO T VALUES (NULL);
已创建 1 行。
SQL> INSERT INTO T VALUES (NULL);
已创建 1 行。
但有时，Oracle会认为NULL是相同的，比如在GROUP BY和DISTINCT操作中，Oracle会认为所有的NULL都是一类的。另一种情况是在DECODE函数中，如果表达式为DECODE（COL， NULL， 0， 1），当COL的值为NULL时，Oracle会认为输入的NULL与第二个参数的NULL值相匹配，DECODE的结果会返回0。不过这里只是给人感觉NULL值是相等的，Oracle在实现DECODE函数时，仍然通过IS NULL的方式进行判断。对于大多数的常用函数来说，如果输入为NULL，则输出也是NULL。NVL、NVL2、DECODE和||操作是例外。它们在输入参数为NULL时，结果可能不是NULL。因为这些函数都有多个参数，当多个参数不全为NULL时，结果可能不是NULL，如果输入参数均为NULL，那么得到的输出结果也是NULL。NULL的另一特点是一般聚集函数不会处理NULL值。不管是MAX、MIN、AVG，还是SUM，都不会处理NULL。注意“不会处理NULL”，是指聚集函数会直接忽略NULL值记录的存在。当是聚集函数处理的列中包含的全部记录都是NULL时，聚集函数会返回NULL值。复制
SQL> DELETE T WHERE ID = 1;
已删除 1 行。
SQL> SELECT NVL(TO_CHAR(ID), ‘NULL’) FROM T;
NVL(TO_CHAR(ID),‘NULL’)
----------------------------------------
NULL
NULL
SQL> SELECT MAX(ID) FROM T;
MAX(ID)
----------
SQL> SELECT AVG(ID) FROM T;
AVG(ID)
----------
SQL> INSERT INTO T VALUES (1);
已创建 1 行。
聚集函数中比较特殊的是COUNT，COUNT不会返回NULL值，即使表中没有记录，或者COUNT（COL）中，COL列的记录全为NULL，COUNT也会返回0值而不是NULL。此外，COUNT可以计算包含NULL记录在内的记录总数。复制
SQL> SELECT COUNT(*), COUNT(1), COUNT(‘A’), COUNT(ID), COUNT(NULL) FROM T;
COUNT(*) COUNT(1) COUNT(‘A’) COUNT(ID) COUNT(NULL)
3 3 3 1 0
最后简单说明AVG，AVG（COL）等价于SUM（COL）/COUNT（COL），不等价于SUM（COL）/COUNT（*）。
SQL> SELECT AVG(ID), SUM(ID)/COUNT(ID), SUM(ID)/COUNT(*) FROM T;
AVG(ID) SUM(ID)/COUNT(ID) SUM(ID)/COUNT(*)
1 1 .333333333
复制
