Add Not NULL constraint on existing table column having null values

Recently in OTN someone posted a question with scenario as below.

“Add Not NULL constraint in the existing table that has null values”

At first it seems to be simple, Just an AlTER Command should do.
But think what would happen to previous existed NULL on that Column.!!

Let understand it better with field work!

--Create a table with col1 as nullable.
SQL> CREATE TABLE NULL_DATA AS
2 WITH DATA AS (
3 SELECT 'AA' AS COL1 FROM DUAL
4 union all
5 SELECT NULL FROM DUAL
6 UNION all
7 SELECT 'BB' FROM DUAL
8 UNION all
9 select NULL from dual )
10 SELECT COL1 FROM DATA;
Table created.

SQL> desc null_data
Name Null? Type
------ -------- --------------
COL1 VARCHAR2(2)

Col1 is nullable column and also hold Undefined data as NULL.
Now let try to alter COL1 and redefine it as “NOT NULL”.

SQL> ALTER TABLE NULL_DATA MODIFY COL1 NOT NULL;
ALTER TABLE NULL_DATA MODIFY COL1 NOT NULL
*
ERROR at line 1:
ORA-02296: cannot enable (USER) - null values found

As NULL Records exists in table hence we can’t directly alter it to NOT NULL.
As a workaround, we can invoke UPDATE command for modifying NULL value to some default.

UPDATE NULL_DATA
SET COL1 = 0
WHERE COL1 IS NULL;

But suppose we have very big table and updating each NULL records with some Defaults can be tedious and might degrade Update performance on live system.

OR

Use of “ENABLE NOVALIDATE”

It means that the constraint will be checked only for newly DML operations, but it does not have to be true for all existing rows.
This allows current table rows to violate the constraint, while ensuring that all new or modified rows are valid.

SQL> ALTER TABLE NULL_DATA MODIFY COL1 NOT NULL NOVALIDATE;
Table altered.

Let see what happen to our NULL Data.
SQL> select count (*) from null_data where col1 is null;
COUNT (*)
----------
2

It still exists, as previously mentions it allows existing rows to violate the new constraint enforce i.e. it is not validated with newly Not Null constraint.

After the alter let try to insert NULL Data.
SQL> insert into null_data values (null);
Insert into null_data values (null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into (""."NULL_DATA"."COL1")

So Constraint NOT NULL is applicable, For future Data.
Now we have NOT NULL Constraint in place, let check view user_constraint data on same.

SQL> Select table_name , status,Validated, Search_Condition
2 From User_Constraints where table_name in ('NULL_DATA');

TABLE_NAME STATUS VALIDATED SEARCH_CONDITION
-------------- -------- ------------- ------------------
NULL_DATA ENABLED NOT VALIDATED "COL1" IS NOT NULL

In view we have metadata column “VALIDATED”, store information whether constraint was validated against all data or not.

If we have Not Null constraint defined on a column and we try to search it with “IS NULL” conditions, Oracle use intelligence to avoid any access path to get desired data.It knows as we have Not NULL Constraint defined it cant hold NULL data, no point in checking same.

let see how oracle handle such queries with EMP table with empno defined as NOT NULL.
explain plan for
select * from emp where empno is null;
-------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
|* 1 | FILTER | | |
| 2 | TABLE ACCESS FULL| EMP | 14 |
-------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)

It adds a Filter condition (NULL IS NOT NULL) to bypass any operations as it wont be true.

Lets check underlying plan for SQL, when we are searching for NULL Data in our case.
When we have use “ENABLE NOVALIDATE” and table still have some NULL data.

SQL> explain plan for
2 select * from null_data where col1 is null;
Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 2630372128
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 4 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| NULL_DATA | 2 | 4 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL1" IS NULL)

Though on Col1 “NOT NULL” constraint is defined, but still oracle will search for NULL data.
Reason is No validation of constraint against all data, Oracle is forces to perform NULL check against table.

To understand it better, lets take case where before enforcing NOT NULL constraint all data of column will validate as NOT NULL.

SQL> CREATE TABLE NO_NULL_DATA AS
2 WITH DATA AS (
3 SELECT 'AA' AS COL1 FROM DUAL
4 union all
5 SELECT 'BB' FROM DUAL)
6 SELECT COL1 FROM DATA;
Table created.

Table NO_NULL_DATA has all data in col1 as NOT NULL.
Hence can directly alter column as NOT NULL.

SQL> ALTER TABLE NO_NULL_DATA MODIFY COL1 NOT NULL;
Table altered.

Lets check constraint view data.

SQL> Select table_name , status,Validated, Search_Condition
2 From User_Constraints where table_name in ('NO_NULL_DATA');

TABLE_NAME STATUS VALIDATED SEARCH_CONDITION
---------------- -------- ------------- ---------------------
NO_NULL_DATA ENABLED VALIDATED"COL1" IS NOT NULL

VALIDATED metadata, telling all data validated constraint “NOT NULL”

Not let try to fetch NULL Data for such constraint definition and check underlying plan.

SQL> explain plan for
2 select * from NO_NULL_DATA where col1 is null;
Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 4252805003
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 0 (0)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| NO_NULL_DATA | 2 | 6 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)

Check at filtered imposed, “NULL IS NOT NULL”
This can’t be true, hence LIO imposed for such cases will be Zero.No table access will happen.
Oracle didn’t entertain query (IS NULL) and add filter that can’t be true.

Hence for “NOVALIDATE” ,Though constraint on column is NOT NULL optimizer will need to entertain IS NULL Conditions.

For some more info please visit Ask Tom

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle Internal and tagged , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s