not null與check is not null

not null與check is not null區別[@more@]

在OCP SQL部分有這樣一道題：

You need to add a NOT NULL constraint to the QUANTITY column in the PO_DETAIL table. Which statement should you use to complete this task?正確 A. ALTER TABLE po_detail MODIFY (quantity NOT NULL) D. ALTER TABLE po_detail ADD CONSTRAINT quantity_nn NOT NULL(quantity);

NOT NULL ConstraintsA NOT NULL constraint prohibits a column from containing nulls. The NULL keywordby itself does not actually define an integrity constraint, but you can specify it toexplicitly permit a column to contain nulls. You must define NOT NULL and NULLusing inline specification. If you specify neither NOT NULL nor NULL, then the defaultis NULL.

從not null約束的定義我們知道，如果我們要給一個列增加一個not null約束，只能透過modify的方式。那透過add行不行呢？比如alter table po_detail add constraint quantity_nn check(quantity is not null)；我們可以透過這種辦法達到"not null"的效果。那麼check is not null既然能達到“not null”的效果，為什麼Oracle要提出not null約束呢，兩者的區別在哪裡？

SQL> create table t(nn number not null,cnn number check(cnn is not null));Table created.

SQL> select constraint_name,constraint_type,search_condition from user_constraints where table_name = 'T';

CONSTRAINT_NAME CONST SEARCH_CONDITIO

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

SYS_C005420 C "NN" IS NOT NULLSYS_C005421 C cnn is not null

透過上面的例子可以發現，二者的CONSTRAINT_TYPE都是C（CHECK約束），而且，二者的檢查條件也幾乎完全相同，唯一的區別是，Oracle為NOT NULL約束生成檢查條件時自動給列名加上了引號。 Oracle把NOT NULL約束作為約束的一種進行描述，而且為NOT NULL約束提供了專門的語法，難道這一切僅僅是為了方便考慮嗎？ 下面透過一個例子來看看NOT NULL和CHECK的不同之處。
SQL> create table t1 as select nn,cnn,rowid r from t where 1=0;

Table created.

SQL> desc t1
Name Null? Type
----------------------------------------- -------- ----------------------------
NN NOT NULL NUMBER
CNN NUMBER
R ROWID

SQL> select constraint_name,constraint_type from user_constraints where table_name = 'T1';

CONSTRAINT_NAME CONST
--------------- -----
SYS_C005422 C

SQL> alter table t1 add constraint cons_c_nn check(cnn is not null);

Table altered.

SQL> insert into t1 select nn,cnn,rowid from t;

402550 rows created.

SQL> commit;

Commit complete.

SQL> create index ind_t1_nn on t1(nn);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade=>true);

PL/SQL procedure successfully completed.

SQL> set autotrace on;
SQL> select count(*) from t1;

COUNT(*)
----------
402550


Execution Plan
----------------------------------------------------------
Plan hash value: 4079031386

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 204 (4)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IND_T1_NN | 402K| 204 (4)| 00:00:03 |
---------------------------------------------------------------------------

由於具有NOT NULL約束的列上面存在索引，Oracle認為透過全索引掃描可以得到正確的答案而且速度比全表掃描快，因此執行計劃使用了快速全索引掃描。
SQL> drop index ind_t1_nn;

Index dropped.

SQL> create index ind_t1_cnn on t1(cnn);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade=>true);

PL/SQL procedure successfully completed.

SQL> set autot on;
SQL> select count(*) from t1;

COUNT(*)
----------
402550


Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 326 (4)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 402K| 326 (4)| 00:00:04 |
-------------------------------------------------------------------

SQL> select /*+ index_ffs(t1 ind_t1_cnn) */ count(*) from t1;

COUNT(*)
----------
402550


Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 326 (4)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 402K| 326 (4)| 00:00:04 |
-------------------------------------------------------------------

雖然我們知道，根據CHECK約束的條件，COL_CHECK列上不會存在為空的記錄，Oracle使用COL_CHECK上的索引同樣可以得到正確的結果，但是Oracle這一次沒有使用索引。而且，即使給出了提示，Oracle仍然沒有使用索引。這應該是NOT NULL和CHECK的最大區別，NOT NULL成為了列的一種屬性，而CHECK是列的資料的限制條件。 Oracle在確定執行計劃，考慮是否使用索引的時候，只是檢查了列的NOT NULL屬性，而沒有去檢查CHECK約束中的具體約束條件。