Oracle Null 学习与测试_20091209

Oracle Null

参考文档

<>P262

http://www.laoxiong.net/null_value_index.html

 

目标：理解Null的一些特性，减少错误

 

目录

1 Null Overview

3 Indexes and Nulls  

4 Bitmap Indexes and Nulls  

5 NULL值与索引

6 NULL特性说明

7 测试

8 Notes

 

1 Null Overview

A null is the absence of a value in a column of a row. Nulls indicate missing, unknown, or inapplicable data.

A column allows nulls unless a NOT NULL or PRIMARY KEY integrity constraint has been defined for the column.(Null可以用在任何列，除开定义了约束Not Null 或 定义了Primary Key)

Nulls are stored in the database if they fall between columns with data values. In these cases they require 1 byte to store the length of the column (Zero). Trailing nulls in a row require no storage because a new row header signals that remaining columns in the previous row are null. (Null 在列中间时，必须用1字节来存储,在末尾时不存储)

 

Most comparisons between nulls and other values are by definition neither true nor false, but unknown. To identify nulls in SQL, use the IS NULL predicate, Use the SQL function NVL to convert nulls to not-null values. (不能用于比较)

 

Nulls are not indexed, except when the cluster key column value is null or the index is a bitmap index. (不被索引，除非在Cluster and bitmap index中)

 

3 Indexes and Nulls

NULL values in indexes are considered to be distinct except when all the non-NULL values in two or more rows of an index are identical, in which case the rows are considered to be identical. Therefore, UNIQUE indexes prevent rows containing NULL values from being treated as identical. This does not apply if there are no non-NULL values—in other words, if the rows are entirely NULL.

Oracle does not index table rows in which all key columns are NULL, except in the case of bitmap indexes or when the cluster key column value is NULL.

( 不理解这段话 )

 

4 Bitmap Indexes and Nulls

Bitmap indexes include rows that have NULL values, unlike most other types of indexes. Indexing of nulls can be useful for some types of SQL statements, such as queries with the aggregate function COUNT.

 

Select Count (*) from employees;

 

Any bitmap index can be used for this query, because all table rows are indexed, including those that have NULL data. If Nulls were not indexed, then the optimizer could only use indexes on columns with NOT NULL constraints.

 

 

 

5 NULL值与索引

 

null值不能进行索引的真正含义：

实际上对于null值，除了位图索引、CLUSTER表的KEY列，也是有可能索引的。那就是复合索引，也就是多列索引。对于普通的索引，null值不能进行索引的正确理解应该是，对于某一行，索引的所有列的值都是null值时，该行才不能被索引。

 

 

 

Create table t2 as select object_id, object_name from dba_objects where rownum < 1001;

 

删除object_name重复的值

 

delete from t2 where rowid not in (select max(rowid) from t2 group by object_name);

 

 

Create unique index ind_t2_name on t2(object_name);

 

idle> set autotrace on explain

idle> select * from t2 where object_name = 'mouse';

 

no rows selected

 

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT ptimizer=CHOOSE

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T2'

   2    1     INDEX (UNIQUE SCAN) OF 'IND_T2_NAME' (UNIQUE)

  

在object_name不存在Null的情况下，查询走索引

 

 

idle> update t2 set object_name=NULL where object_id=864;

 

1 row updated.

 

idle> commit;

 

Commit complete.

 

在object_name存在Null的情况下，重新查询

 

idle> select object_id,object_name from t2 where object_name is null;

 

 OBJECT_ID

----------

OBJECT_NAME

--------------------------------------------------------------------

       864

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT ptimizer=CHOOSE

   1    0   TABLE ACCESS (FULL) OF 'T2'

 

只是对于该行不能走索引，其它行仍可以.

 

idle> select object_id, object_name from t2 where object_name = 'V_$NLS_VALID_VALUES';

 

 OBJECT_ID

----------

OBJECT_NAME

----------------------------------------------------------------------------------------------------

       862

V_$NLS_VALID_VALUES

 

 

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT ptimizer=CHOOSE

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T2'

   2    1     INDEX (UNIQUE SCAN) OF 'IND_T2_NAME' (UNIQUE)

 

复合索引

 

Create  index ind_t2_composite on t2(object_name,SUBOBJECT_NAME);

 

idle> analyze table t2 compute statistics for table for all indexes for all indexed columns;

 

Table analyzed.

 

idle> select object_id,object_name from t2 where object_name is null;

 

 OBJECT_ID

----------

OBJECT_NAME

-----------------------------------------------------------------------

     864

    

 

 

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=1 Bytes=27)

   1    0   TABLE ACCESS (FULL) OF 'T2' (Cost=3 Card=1 Bytes=27)

 

没有走索引，因为(object_name，SUBOBJECT_NAME)的组合可能为(null,null).

 

将SUBOBJECT_NAME 加约束条件 Not Null

 

idle> alter table t2 modify SUBOBJECT_NAME not null;

 

Table altered.

 

idle> select object_id,object_name from t2 where object_name is null;

 

 OBJECT_ID

----------

OBJECT_NAME

-----------------------------------------------------------------------

     864

    

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=24)

   1    0   INDEX (RANGE SCAN) OF 'IND_T2_COMPOSITE' (NON-UNIQUE) (Cost=2 Card=1 Bytes=24)

 

走了索引，因为(object_name，SUBOBJECT_NAME)的组合不可能为(null,null).

 

为了在查询Null列所在的行时走索引，有一种更简便的方法，不需要与其他列建立复合索引，而是与一个常量值建立复合索引：

 

create index t1_idx on t2(object_name,0);

 

 

idle> select object_id,object_name from t2 where object_name is null;

 

 OBJECT_ID

----------

OBJECT_NAME

-----------------------------------------------------------------------

     864

    

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=1 Bytes=24)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=3 Card=1 Bytes=24)

   2    1     INDEX (RANGE SCAN) OF 'T1_IDX' (NON-UNIQUE) (Cost=2 Card=1)

 

 

6 NULL特性说明

 

1、等价于没有任何值、是未知数。

2、NULL与0、空字符串、空格都不同。

3、对空值做加、减、乘、除等运算操作，结果仍为空。

4、NULL的处理使用NVL函数。

5、比较时使用关键字用“is null”和“is not null”。

6、空值不能被索引，所以查询时有些符合条件的数据可能查不出来，count(*)中，

用nvl(列名,0)处理后再查。

7、排序时比其他数据都大（索引默认是降序排列，小→大），所以NULL值总是排在最后。

 

使用方法：

SQL> select 1 from dual where null=null;

no rows selected

 

SQL> select 1 from dual where null='';

no rows selected

 

SQL> select 1 from dual where ''=''; 这样表示什么? 空字符串？

no rows selected

 

SQL> select 1 from dual where null is null;

        1

---------

        1

SQL> select 1 from dual where nvl(null,0)=nvl(null,0);

        1

---------

        1

对空值做加、减、乘、除等运算操作，结果仍为空。

SQL> select 1+null from dual;

SQL> select 1-null from dual;

SQL> select 1*null from dual;

idle>  select 1/null from dual;

 

    1/NULL

----------

 

查询到一个记录.

 

注：这个记录就是SQL语句中的那个null

设置某些列为空值

update table1 set 列1=NULL where 列1 is not null;

 

create table sale (month char(6),sell number);

insert into sale values('200001',1000);

insert into sale values('200002',1100);

insert into sale(month) values('200009');（注意：这条记录的sell值为空）

 

commit;

 

SQL> select * from sale;

 

MONTH        SELL

------ ----------

200001       1000

200002       1100

200009

 

SQL> select * from sale where sell like '%';

 

MONTH        SELL

------ ----------

200001       1000

200002       1100

 

结果说明：

查询结果说明此SQL语句查询不出列值为NULL的字段

此时需对字段为NULL的情况另外处理。

SQL> select * from sale where sell like '%' or sell is null;

SQL> select * from sale where nvl(sell,0) like '%';

 

MONTH        SELL

------ ----------

200001       1000

200002       1100

200009

 

7 测试

 

7.1 Primary key and Not Null 约束不允许Null

 

 

create table t_null( id number primary key, name varchar2(20));

 

Table created.

 

idle> insert into t_null values(1,'mouse');

 

1 row created.

 

idle> commit;

 

Commit complete.

 

idle> insert into t_null values(null,'dog');

insert into t_null values(null,'dog')

*

ERROR at line 1:

ORA-01400: cannot insert NULL into ("TEST"."T_NULL"."ID")

 

 

idle> insert into t_null values(2,null);

 

1 row created.

 

idle> alter table t_null modify name not null;

 

Table altered.

 

idle> insert into t_null values(2,null);

insert into t_null values(2,null)

*

ERROR at line 1:

ORA-01400: cannot insert NULL into ("TEST"."T_NULL"."NAME")

 

 

8 Notes

1)  you could build a unique index on a nullable column, but that the nulls would not be indexed.唯一性索引上能够有null值，但Null所在行不会被索引

 

2) 索引块的Dump文件格式有待进一步研究

参考<< A null oddity_20091209.doc>>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10248702/viewspace-622169/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10248702/viewspace-622169/