ORACLE数据库基础管理-null值问题总结以及is null性能优化

1. NULL值总结

2. select id from table1 使用全表扫描，id'列存储在索引，由于id列未指定为非null模式，而且索引不存储NULL值，索引只能进行全表扫描。
3. 只有在update时候才能使用update t set a=null where 形式
4. 对not in 和not exists的影响。如果列未指定为非空，则not exists性能较高，指定了非空，则not in和not exists类似。
5. 优化is null，索引不会存储NULL值(1 用nvl函数改写，或者创建一个组合函数（id,1）

SQL> SET AUTOTRACE TRACEONLY SQL> SELECT *FROM T1 WHERE OBJECT_NAME IS NULL; 已选择9行。 执行计划  create index idx2 on T1(OBJECT_NAME,1); ---------------------------------------------------------- Plan hash value: 2797148358 --------------------------------------------------------------------------------   | Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)|  Time     | Pstart| Pstop | --------------------------------------------------------------------------------   |   0 | SELECT STATEMENT              |      |     1 |    95 |     2   (0)|  00:00:01 |       |       |   |   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T1   |     1 |    95 |     2   (0)|  00:00:01 |     1 |     1 |   |*  2 |   INDEX RANGE SCAN                 | IDX2 |     1 |       |     2   (0)|  00:00:01 |       |       |   -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_NAME" IS NULL) 统计信息 ----------------------------------------------------------           1  recursive calls           0  db block gets           6  consistent gets           0  physical reads           0  redo size        1967  bytes sent via SQL*Net to client         415  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           9  rows processed

1. Null值对排序的影响

desc排序，null值在最前面。 asc排序，null值在最后面。 在order by 关键字中加入nulls last或者nulls first; select affectuser ,length(affectuser) from tjnscs.nc_notice_large_area_users b order by length(affectuser) desc nulls last;

1. Null值对子查询的影响

1 在多行子查询中，子查询结果包含null，则不能使用not in。

2 Null值只能用is is not null关系符，除非update 指定=null

3 null值与任何字符进行运算均为false。

SELECT COUNT(1)FROM T1 WHERE NULL=NULL 返回0

SELECT COUNT(1)FROM T1 WHERE NULL IS NULL 正常返回。

 

1. Null值对运算的影响

对于存在null值得number列进行运算，对于null列会存在问题。

create table a1 (id  number,id1 number);

iNSERT INTO A1

SELECT OBJECT_ID,OBJECT_ID+10 FROM DBA_OBJECTS WHERE ROWNUM<10;

select *from a1;

update a1 set id1=null where rownum<3;

select id+id1 from a1;

 

1. Null值对count的影响

SELECT COUNT(1) FROM DBA_USERS;

SELECT COUNTEXPIRY_DATE)FROM DBA_USERS;

SELECT COUNT(DISTINCT EXPIRY_DATE) FROM DBA_USERS;

 

结果都是不同的(任意值不包含列值)—>则计算null值。          

               COUNT(+列值) 去除null，包含重复

               COUNT(distinct+列值) 去除null，去除重复