Not null and check is not null

Http://boylook.itpub.net/post/43144/520538

The ocp SQL section has the following question:

You need to add a not null constraint to the QUANTITY column in the PO_DETAIL table. Which statement shocould you use to complete this task? Correct 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.

From the definition of the not null constraint, we know that if we want to add a not null constraint to a column, we can only use the modify method. Can't you use add? For example, alter table po_detail add constraint quantity_nn check (quantity is not null); we can achieve the "not null" effect through this method. So since check is not null can achieve the "not null" effect, why does Oracle propose the not null constraint? What is the difference between the two?

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

Through the above example, we can find that the CONSTRAINT_TYPE of both are CCHECK constraints), and the check conditions of the two are almost identical. The only difference is that, oracle automatically quotes column names when not null constraints are generated. Oracle describes the not null constraint as a constraint and provides a special syntax for the not null constraint. Is all this just for convenience? The following example shows the differences between not null and 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 |
---------------------------------------------------------------------------

Because an index exists on a column with the not null constraint, Oracle considers that full index scan can obtain the correct answer and the speed is faster than full table scan. Therefore, the execution plan uses quick full index scan.
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 |
-------------------------------------------------------------------

Although we know that there will be no blank records in the COL_CHECK column according to the CHECK constraints, Oracle uses the index on COL_CHECK to get the correct results, but Oracle does not use the index this time. In addition, Oracle still does not use indexes even if a prompt is given. This should be the biggest difference between not null and CHECK. not null is a type of attribute of a column, and CHECK is the condition of column data. When determining the execution plan and whether to use the index, Oracle only checks the not null attribute of the column, but does not check the specific constraints in the CHECK constraint.

This article is from "MIKE's old blog" blog, please be sure to keep this source http://boylook.blog.51cto.com/7934327/1298600