Jonathan Lewis’ update on his post “Constraints, Inserts and bind” prompted another thought: is there a difference between a check constraint (IS NOT NULL) and a NOT NULL column declaration with regards to recursive queries that are run during parsing?
Let’s start with “CHECK(xyz IS NOT NULL)” constraints:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | create table t2 ( n1 number check(n1 is not null) , v1 varchar2(10) check(v1 is not null));exec dbms_monitor.session_trace_enable(null, null, true, false)begin for idx in 1..100 loop execute immediate 'insert into t2 values (' || idx || ', ''ABC'')'; end loop;end;/commit;exec dbms_monitor.session_trace_disable(null, null) |
Next, we replace the check constraints with “NOT NULL” declarations.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | create table t1 ( n1 number not null , v1 varchar2(10) not null);exec dbms_monitor.session_trace_enable(null, null, true, false)begin for idx in 1..100 loop execute immediate 'insert into t1 values (' || idx || ', ''ABC'')'; end loop;end;/commit;exec dbms_monitor.session_trace_disable(null, null) |
The SQL trace file from the check constraints shows 107 distinct SQLs. One of them being (as one would expect after reading Jonathan’s post):
1 | select condition from cdef$ where rowid=:1 |
The trace file from the “NOT NULL” declarations shows 106 distinct SQLs. You know which one’s not in there, right? 
When every LIO counts, use “NOT NULL” declarations over “CHECK (xzy IS NOT NULL)” constraints!