Check Constraint & NOT NULL Constraint

Learnt something newly in Oracle database, its about Check constraint and NOT NULL constraints.


rajesh@ORA11GR2>
rajesh@ORA11GR2> create table t
  2  (
  3     x number not null,
  4     y number check (y is not null),
  5     z number,
  6     constraint t_chk check(z is not null)
  7  )
  8  nologging;

Table created.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> insert /*+ append */ into h
  2  select level,level,level
  3  from dual
  4  connect by level <=1000000;

1000000 rows created.

Elapsed: 00:00:01.10
rajesh@ORA11GR2>
rajesh@ORA11GR2> commit;

Commit complete.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.04
rajesh@ORA11GR2>

Now, we have defined NOT NULL constraint on column 'x' and column level check constraint in 'y' and table level check constraint on column 'z'.


rajesh@ORA11GR2>
rajesh@ORA11GR2> select constraint_type,
  2  search_condition
  3  from user_constraints
  4  where table_name ='T'
  5  /

CONST SEARCH_CONDITION
----- -------------------------
C     "X" IS NOT NULL
C     y is not null
C     z is not null

Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2>

The NOT NULL constraint is Technically implemented in the database as CHECK constraint.  But the real difference comes into picture how the optimizer understand NOTNULL constraint and CHECK constraint.


rajesh@ORA11GR2> create index t_ind on t(x) nologging;

Index created.

Elapsed: 00:00:00.64
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select /*+ index(t,t_ind) */ count(*)
  2  from t
  3  /
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 646498162

------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |  2239   (1)| 00:00:27 |
|   1 |  SORT AGGREGATE  |       |     1 |            |          |
|   2 |   INDEX FULL SCAN| T_IND |  1000K|  2239   (1)| 00:00:27 |
------------------------------------------------------------------

rajesh@ORA11GR2>

With the index on column X, the optimizer knew that X was not null and the index on X therefore pointed to every row in the table, so its safe to count the rows via the Index.

If we drop and recreate the index on column Y, the optimizer cannot use this new index.


rajesh@ORA11GR2> drop index t_ind;

Index dropped.

Elapsed: 00:00:00.06
rajesh@ORA11GR2> create index t_ind on t(y) nologging;

Index created.

Elapsed: 00:00:00.68
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select /*+ index(t,t_ind) */ count(*)
  2  from t
  3  /
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   771   (2)| 00:00:10 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    |  1000K|   771   (2)| 00:00:10 |
-------------------------------------------------------------------

rajesh@ORA11GR2>

In this case the optimizer is not aware of that Y was NOT NULL, because this check constraint doesn't provide enough information for the optimizer & entirely null entries will not be preserved in B*Tree index. and because Y was assumed by the optimizer to allow for NULL values, So the optimizer could not use this index.

The same applies to column 'Z'.


rajesh@ORA11GR2> drop index t_ind;

Index dropped.

Elapsed: 00:00:00.04
rajesh@ORA11GR2> create index t_ind on t(z) nologging;

Index created.

Elapsed: 00:00:00.67
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select /*+ index(t,t_ind) */ count(*)
  2  from t
  3  /
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   771   (2)| 00:00:10 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    |  1000K|   771   (2)| 00:00:10 |
-------------------------------------------------------------------

rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2>