{"id":4176,"date":"2022-02-27T18:02:49","date_gmt":"2022-02-27T18:02:49","guid":{"rendered":"https:\/\/nenadnoveljic.com\/blog\/?p=4176"},"modified":"2022-02-27T18:02:52","modified_gmt":"2022-02-27T18:02:52","slug":"nvl-cardinality-in-oracle-21c","status":"publish","type":"post","link":"https:\/\/nenadnoveljic.com\/blog\/nvl-cardinality-in-oracle-21c\/","title":{"rendered":"NVL Cardinality in Oracle 21c"},"content":{"rendered":"<h1>IS NULL vs. NVL<\/h1>\n<p>IS NULL is the SQL expression for selecting NULL values. Nevertheless, some developers abuse NVL for that purpose. In this blog post, I&#8217;ll layout the drawbacks of the approach with NVL, and also show how the cardinality estimate improved in Oracle 21c.<\/p>\n<p>I&#8217;ll use a simple table T1 with the following properties:<\/p>\n<ul>\n<li>The table contains 110,000 rows.<\/li>\n<li>The column D of data type DATE contains 100,000 null values.<\/li>\n<\/ul>\n<pre><code>drop table t1 ;\n\ncreate table t1 as select\n  rownum n,\n  trunc(sysdate) d\n  from dual connect by level &lt;= 1e4 ;\n\ninsert into t1 \n  select rownum + 1e4, null \n  from dual connect by level &lt;= 1e5 ;\n\ncommit ;\n\nexec dbms_stats.gather_table_stats(null, 'T1');<\/code><\/pre>\n<p>IS NULL predicate is the most reliable way to get a good cardinality estimate when selecting NULL values, for example:<\/p>\n<pre><code>select * from t1 where d is null ;\n\n--------------------------------------------------------------------------\n| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |\n--------------------------------------------------------------------------\n|   0 | SELECT STATEMENT  |      |   100K|   683K|    47   (5)| 00:00:01 |\n|*  1 |  TABLE ACCESS FULL| T1   |   100K|   683K|    47   (5)| 00:00:01 |\n--------------------------------------------------------------------------<\/code><\/pre>\n<p>The equivalent NVL expression for extracting NULL values is as follows:<\/p>\n<pre><code>select * from t1 \n  where nvl(d, to_date('31.12.2200','dd.mm.yyyy')) = to_date('31.12.2200','dd.mm.yyyy') ;\n\n--------------------------------------------------------------------------\n| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |\n--------------------------------------------------------------------------\n|   0 | SELECT STATEMENT  |      |   100K|   683K|    48   (7)| 00:00:01 |\n|*  1 |  TABLE ACCESS FULL| T1   |   100K|   683K|    48   (7)| 00:00:01 |\n--------------------------------------------------------------------------<\/code><\/pre>\n<p>As long as the column D doesn&#8217;t contain the value &#8216;31.12.2200&#8217;, the NVL expression evaluates to TRUE only for NULL values. As we can see, optimizer recognized the intention behind the expression and produced a good cardinality estimate. <a href=\"https:\/\/jonathanlewis.wordpress.com\/2013\/08\/13\/nvl\/\">Jonathan Lewis already described how optimizer successfully handles that case<\/a>.<\/p>\n<p>But using NVL for selecting NULL values has disadvantages. Firstly, the logical correctness of the NVL expression depends on the assumption that the hardcoded value will never become a regular value and inserted into the table. That, however, might change in the lifespan of the application. In that case, the developers will need to search for all the hardcoded values and rewrite them. Secondly, NVL can yield a subpar cardinality estimate.<\/p>\n<h1>Join with NVL<\/h1>\n<p>Let&#8217;s slightly modify the query above to select the rows where D is above an in-range threshold or NULL:<\/p>\n<pre><code>select * from t1 where d &gt;= trunc(sysdate) or d is null ;\n\n--------------------------------------------------------------------------\n| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |\n--------------------------------------------------------------------------\n|   0 | SELECT STATEMENT  |      |   100K|   689K|    47   (5)| 00:00:01 |\n|*  1 |  TABLE ACCESS FULL| T1   |   100K|   689K|    47   (5)| 00:00:01 |\n--------------------------------------------------------------------------<\/code><\/pre>\n<p>As we can see, the equivalent query with NVL still gets a good cardinality estimate:<\/p>\n<pre><code>select * from t1 where nvl(d, to_date('31.12.2200','dd.mm.yyyy')) &gt;= trunc(sysdate) ;\n\n--------------------------------------------------------------------------\n| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |\n--------------------------------------------------------------------------\n|   0 | SELECT STATEMENT  |      |   110K|   751K|    52  (14)| 00:00:01 |\n|*  1 |  TABLE ACCESS FULL| T1   |   110K|   751K|    52  (14)| 00:00:01 |\n--------------------------------------------------------------------------<\/code><\/pre>\n<p>But the estimate goes awry after joining the result set above with another table.<\/p>\n<p>I&#8217;m creating the table T2, similar to T1, only without NULL values:<\/p>\n<pre><code>create table t2 as select\n  rownum n,\n  trunc(sysdate) d\n  from dual connect by level &lt;= 1e5 ;<\/code><\/pre>\n<p>The following query with IS NULL returns 100K rows.<\/p>\n<pre><code>select * from t1, t2 \n  where t1.n = t2.n and ( t2.d &lt;= t1.d or t1.d is null );<\/code><\/pre>\n<p>The cardinality estimate is close to the actual number of rows:<\/p>\n<pre><code>-----------------------------------------------------------------------------------\n| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |\n-----------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT   |      | 91736 |  1791K|       |   315   (2)| 00:00:01 |\n|*  1 |  HASH JOIN         |      | 91736 |  1791K|  2048K|   315   (2)| 00:00:01 |\n|   2 |   TABLE ACCESS FULL| T1   |   110K|   751K|       |    46   (3)| 00:00:01 |\n|   3 |   TABLE ACCESS FULL| T2   |   100K|  1269K|       |    48   (3)| 00:00:01 |\n-----------------------------------------------------------------------------------<\/code><\/pre>\n<p>But the estimate is by one order of magnitude lower for the equivalent query with NVL:<\/p>\n<pre><code>select * from t1, t2 \n  where t1.n = t2.n and t2.d &lt;= nvl(t1.d,to_date('31.12.2200','dd.mm.yyyy'))   ;\n\n-----------------------------------------------------------------------------------\n| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |\n-----------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT   |      |  9091 |   177K|       |   315   (2)| 00:00:01 |\n|*  1 |  HASH JOIN         |      |  9091 |   177K|  2048K|   315   (2)| 00:00:01 |\n|   2 |   TABLE ACCESS FULL| T1   |   110K|   751K|       |    46   (3)| 00:00:01 |\n|   3 |   TABLE ACCESS FULL| T2   |   100K|  1269K|       |    48   (3)| 00:00:01 |\n-----------------------------------------------------------------------------------<\/code><\/pre>\n<p>The execution plan above is from Oracle release 19c (tested on 19.7 and 19.14):<\/p>\n<p>In the releae 21c (tested on 21.4), the cardinality calculation has improved. Actually, the estimate is identical to the one of the query with IS NULL:<\/p>\n<pre><code>select * from t1, t2 \n  where t1.n = t2.n and t2.d &lt;= nvl(t1.d,to_date('31.12.2200','dd.mm.yyyy'))   ;\n\n-----------------------------------------------------------------------------------\n| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |\n-----------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT   |      | 91736 |  1791K|       |   317   (3)| 00:00:01 |\n|*  1 |  HASH JOIN         |      | 91736 |  1791K|  2048K|   317   (3)| 00:00:01 |\n|   2 |   TABLE ACCESS FULL| T1   |   110K|   751K|       |    47   (5)| 00:00:01 |\n|   3 |   TABLE ACCESS FULL| T2   |   100K|  1269K|       |    49   (5)| 00:00:01 |\n-----------------------------------------------------------------------------------<\/code><\/pre>\n<h1>Conclusion<\/h1>\n<p>In conclusion, IS NULL is a better way than NVL for filtering NULL values. The logic based on NVL can become incorrect as data changes. Besides that, cardinality estimates with IS NULL are generally more reliable. Although Oracle has been improving NVL cardinality estimates, you might hit a corner case where an improvement hasn&#8217;t been implemented yet.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Reasons for not using NVL for filtering NULL values. Join cardinality estimate improvement in Oracle 21c. <a href=\"https:\/\/nenadnoveljic.com\/blog\/nvl-cardinality-in-oracle-21c\/\" class=\"more-link\">Continue Reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[46,11,5,1],"tags":[],"class_list":["post-4176","post","type-post","status-publish","format-standard","hentry","category-21c","category-cost-based-optimizer","category-oracle","category-uncategorized"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>NVL Cardinality in Oracle 21c - All-round Database Topics<\/title>\n<meta name=\"description\" content=\"Reasons for not using NVL for filtering NULL values. Join cardinality estimate improvement in Oracle 21c.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/nenadnoveljic.com\/blog\/nvl-cardinality-in-oracle-21c\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"NVL Cardinality in Oracle 21c - All-round Database Topics\" \/>\n<meta property=\"og:description\" content=\"Reasons for not using NVL for filtering NULL values. Join cardinality estimate improvement in Oracle 21c.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/nenadnoveljic.com\/blog\/nvl-cardinality-in-oracle-21c\/\" \/>\n<meta property=\"og:site_name\" content=\"All-round Database Topics\" \/>\n<meta property=\"article:published_time\" content=\"2022-02-27T18:02:49+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-02-27T18:02:52+00:00\" \/>\n<meta name=\"author\" content=\"Nenad Noveljic\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@NenadNoveljic\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Nenad Noveljic\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/nvl-cardinality-in-oracle-21c\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/nvl-cardinality-in-oracle-21c\\\/\"},\"author\":{\"name\":\"Nenad Noveljic\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"headline\":\"NVL Cardinality in Oracle 21c\",\"datePublished\":\"2022-02-27T18:02:49+00:00\",\"dateModified\":\"2022-02-27T18:02:52+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/nvl-cardinality-in-oracle-21c\\\/\"},\"wordCount\":424,\"commentCount\":0,\"articleSection\":[\"21c\",\"cost based optimizer\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/nvl-cardinality-in-oracle-21c\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/nvl-cardinality-in-oracle-21c\\\/\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/nvl-cardinality-in-oracle-21c\\\/\",\"name\":\"NVL Cardinality in Oracle 21c - All-round Database Topics\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\"},\"datePublished\":\"2022-02-27T18:02:49+00:00\",\"dateModified\":\"2022-02-27T18:02:52+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"description\":\"Reasons for not using NVL for filtering NULL values. Join cardinality estimate improvement in Oracle 21c.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/nvl-cardinality-in-oracle-21c\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/nvl-cardinality-in-oracle-21c\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/nvl-cardinality-in-oracle-21c\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"NVL Cardinality in Oracle 21c\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\",\"name\":\"All-round Database Topics\",\"description\":\"Nenad Noveljic\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\",\"name\":\"Nenad Noveljic\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"caption\":\"Nenad Noveljic\"},\"sameAs\":[\"nenad-noveljic-9b746a6\",\"https:\\\/\\\/x.com\\\/NenadNoveljic\"],\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/author\\\/nenad\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"NVL Cardinality in Oracle 21c - All-round Database Topics","description":"Reasons for not using NVL for filtering NULL values. Join cardinality estimate improvement in Oracle 21c.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/nenadnoveljic.com\/blog\/nvl-cardinality-in-oracle-21c\/","og_locale":"en_US","og_type":"article","og_title":"NVL Cardinality in Oracle 21c - All-round Database Topics","og_description":"Reasons for not using NVL for filtering NULL values. Join cardinality estimate improvement in Oracle 21c.","og_url":"https:\/\/nenadnoveljic.com\/blog\/nvl-cardinality-in-oracle-21c\/","og_site_name":"All-round Database Topics","article_published_time":"2022-02-27T18:02:49+00:00","article_modified_time":"2022-02-27T18:02:52+00:00","author":"Nenad Noveljic","twitter_card":"summary_large_image","twitter_creator":"@NenadNoveljic","twitter_misc":{"Written by":"Nenad Noveljic","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/nenadnoveljic.com\/blog\/nvl-cardinality-in-oracle-21c\/#article","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/nvl-cardinality-in-oracle-21c\/"},"author":{"name":"Nenad Noveljic","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"headline":"NVL Cardinality in Oracle 21c","datePublished":"2022-02-27T18:02:49+00:00","dateModified":"2022-02-27T18:02:52+00:00","mainEntityOfPage":{"@id":"https:\/\/nenadnoveljic.com\/blog\/nvl-cardinality-in-oracle-21c\/"},"wordCount":424,"commentCount":0,"articleSection":["21c","cost based optimizer","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/nenadnoveljic.com\/blog\/nvl-cardinality-in-oracle-21c\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/nenadnoveljic.com\/blog\/nvl-cardinality-in-oracle-21c\/","url":"https:\/\/nenadnoveljic.com\/blog\/nvl-cardinality-in-oracle-21c\/","name":"NVL Cardinality in Oracle 21c - All-round Database Topics","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#website"},"datePublished":"2022-02-27T18:02:49+00:00","dateModified":"2022-02-27T18:02:52+00:00","author":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"description":"Reasons for not using NVL for filtering NULL values. Join cardinality estimate improvement in Oracle 21c.","breadcrumb":{"@id":"https:\/\/nenadnoveljic.com\/blog\/nvl-cardinality-in-oracle-21c\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/nenadnoveljic.com\/blog\/nvl-cardinality-in-oracle-21c\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/nenadnoveljic.com\/blog\/nvl-cardinality-in-oracle-21c\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/nenadnoveljic.com\/blog\/"},{"@type":"ListItem","position":2,"name":"NVL Cardinality in Oracle 21c"}]},{"@type":"WebSite","@id":"https:\/\/nenadnoveljic.com\/blog\/#website","url":"https:\/\/nenadnoveljic.com\/blog\/","name":"All-round Database Topics","description":"Nenad Noveljic","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/nenadnoveljic.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa","name":"Nenad Noveljic","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","caption":"Nenad Noveljic"},"sameAs":["nenad-noveljic-9b746a6","https:\/\/x.com\/NenadNoveljic"],"url":"https:\/\/nenadnoveljic.com\/blog\/author\/nenad\/"}]}},"_links":{"self":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/4176","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/comments?post=4176"}],"version-history":[{"count":1,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/4176\/revisions"}],"predecessor-version":[{"id":4181,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/4176\/revisions\/4181"}],"wp:attachment":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/media?parent=4176"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/categories?post=4176"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/tags?post=4176"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}