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!