Oracle Null

參考文件

<>P262

目標：理解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>>

Oracle Null 學習與測試_20091209

相關文章