{"id":313,"date":"2018-01-27T15:05:58","date_gmt":"2018-01-27T15:05:58","guid":{"rendered":"https:\/\/www.spotonoracle.com\/?p=313"},"modified":"2018-01-27T15:05:58","modified_gmt":"2018-01-27T15:05:58","slug":"parse-overhead-not-null-vs-checkxyz-is-not-null","status":"publish","type":"post","link":"https:\/\/www.spotonoracle.com\/?p=313","title":{"rendered":"Parse overhead: NOT NULL vs CHECK(xyz IS NOT NULL)"},"content":{"rendered":"<p>Jonathan Lewis&#8217; update on his post <a href=\"https:\/\/jonathanlewis.wordpress.com\/2006\/12\/14\/constraints-inserts-and-bind\/\" rel=\"noopener\" target=\"_blank\">&#8220;Constraints, Inserts and bind&#8221;<\/a> 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?<\/p>\n<p>Let&#8217;s start with &#8220;CHECK(xyz IS NOT NULL)&#8221; constraints:<\/p>\n<pre class=\"brush: sql; collapse: false; highlight: [2,3]; title: ; wrap-lines: false; notranslate\" title=\"\">\r\ncreate table t2 (\r\n    n1 number       check(n1 is not null)\r\n  , v1 varchar2(10) check(v1 is not null)\r\n)\r\n;\r\n\r\nexec dbms_monitor.session_trace_enable(null, null, true, false)\r\nbegin\r\n  for idx in 1..100 loop\r\n    execute immediate 'insert into t2 values (' || idx || ', ''ABC'')';\r\n  end loop;\r\nend;\r\n\/\r\ncommit;\r\nexec dbms_monitor.session_trace_disable(null, null)\r\n<\/pre>\n<p>Next, we replace the check constraints with &#8220;NOT NULL&#8221; declarations.<\/p>\n<pre class=\"brush: sql; collapse: false; highlight: [2,3]; title: ; wrap-lines: false; notranslate\" title=\"\">\r\ncreate table t1 (\r\n    n1 number       not null\r\n  , v1 varchar2(10) not null\r\n)\r\n;\r\n\r\nexec dbms_monitor.session_trace_enable(null, null, true, false)\r\nbegin\r\n  for idx in 1..100 loop\r\n    execute immediate 'insert into t1 values (' || idx || ', ''ABC'')';\r\n  end loop;\r\nend;\r\n\/\r\ncommit;\r\nexec dbms_monitor.session_trace_disable(null, null)\r\n<\/pre>\n<p>The SQL trace file from the check constraints shows 107 distinct SQLs. One of them being (as one would expect after reading Jonathan&#8217;s post):<\/p>\n<pre class=\"brush: sql; collapse: false; title: ; wrap-lines: false; notranslate\" title=\"\">\r\nselect condition from cdef$ where rowid=:1\r\n<\/pre>\n<p><a href=\"https:\/\/www.spotonoracle.com\/wp-content\/uploads\/2018\/01\/trace-snippet_2018-01_check-recursive.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.spotonoracle.com\/wp-content\/uploads\/2018\/01\/trace-snippet_2018-01_check-recursive.png\" alt=\"\" width=\"263\" height=\"300\" class=\"alignnone size-medium wp-image-314\" srcset=\"https:\/\/www.spotonoracle.com\/wp-content\/uploads\/2018\/01\/trace-snippet_2018-01_check-recursive.png 638w, https:\/\/www.spotonoracle.com\/wp-content\/uploads\/2018\/01\/trace-snippet_2018-01_check-recursive-263x300.png 263w, https:\/\/www.spotonoracle.com\/wp-content\/uploads\/2018\/01\/trace-snippet_2018-01_check-recursive-624x711.png 624w\" sizes=\"auto, (max-width: 263px) 100vw, 263px\" \/><\/a><\/p>\n<p>The trace file from the &#8220;NOT NULL&#8221; declarations shows 106 distinct SQLs. You know which one&#8217;s not in there, right? \ud83d\ude09<\/p>\n<p>When every LIO counts, use &#8220;NOT NULL&#8221; declarations over &#8220;CHECK (xzy IS NOT NULL)&#8221; constraints!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Jonathan Lewis&#8217; update on his post &#8220;Constraints, Inserts and bind&#8221; 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&#8217;s start with &#8220;CHECK(xyz IS NOT NULL)&#8221; constraints: create table t2 ( n1 number check(n1 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-313","post","type-post","status-publish","format-standard","hentry","category-internals"],"_links":{"self":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/313","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=313"}],"version-history":[{"count":4,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/313\/revisions"}],"predecessor-version":[{"id":318,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/313\/revisions\/318"}],"wp:attachment":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=313"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=313"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=313"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}