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:

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.

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):

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!