{"id":93390,"date":"2022-02-22T19:29:59","date_gmt":"2022-02-22T19:29:59","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=93390"},"modified":"2022-03-18T21:21:59","modified_gmt":"2022-03-18T21:21:59","slug":"the-effects-of-null-with-not-in-on-oracle-transformations","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/the-effects-of-null-with-not-in-on-oracle-transformations\/","title":{"rendered":"The effects of NULL with NOT IN on Oracle transformations"},"content":{"rendered":"

Jonathan Lewis' continuing series on the Oracle optimizer and how it transforms queries into execution plans:<\/strong><\/p>\n

    \n
  1. Transformations by the Oracle Optimizer<\/a><\/li>\n
  2. The effects of NULL with NOT IN on Oracle transformations<\/a><\/li>\n
  3. Oracle subquery caching and subquery pushing<\/a><\/li>\n
  4. Oracle optimizer removing or coalescing subqueries<\/a><\/li>\n
  5. Oracle optimizer Or Expansion Transformations<\/a><\/li>\n<\/ol>\n\n

    In my first article on transformations<\/a>, I described how Oracle liked to minimize the number of query blocks that the optimizer had to handle. I presented the example of an IN<\/code> subquery<\/em> in the where<\/code> clause that Oracle would rewrite as the equivalent EXISTS<\/code> subquery<\/em> before unnesting the subquery to produce a join. The join might be a semi-join<\/em> or partial join<\/em> but might include an in-line aggregate view<\/em>, which might be further transformed by complex view merging<\/em>. This article considers effects of NULL with NOT IN on Oracle transformations.<\/p>\n

    There are many more transformations that the optimizer can apply to subqueries, but before moving on to any of the more subtle and complicated transformations, it\u2019s important to look at the effects of nulls<\/em><\/strong> and the impact they have when IN<\/code> is changed to NOT<\/code> IN<\/code>.<\/p>\n

    To be or not to be<\/h2>\n

    Or, to relate Hamlet\u2019s question to subqueries and the optimizer: IN<\/code> or NOT<\/code> IN<\/code>. Before pursuing the topic of subqueries any further, it\u2019s important to keep in mind that there is an essential difference between IN subqueries and NOT<\/code> IN<\/code> subqueries. While NOT<\/code> EXISTS<\/code> is the opposite of EXISTS<\/code>, NOT IN is not<\/strong> the exact opposite of IN<\/code> and if you forget this you may end up rewriting a query in a way that produces the wrong results.<\/p>\n

    The problem is best explained with a basic definition followed by a simple example:<\/p>\n

      \n
    • IN()<\/code> is equivalent to equals ANY()<\/code><\/li>\n
    • NOT IN()<\/code> is equivalent to not equal to ALL()<\/code><\/li>\n<\/ul>\n

      Think about what happens if the result set of your subquery includes a NULL<\/code> \u2013 to keep it simple you can start with a list of values rather than a subquery then consider the two expressions:<\/p>\n

        \n
      • 3 IN (1, 3, 5, null)<\/li>\n
      • 4 NOT IN (1, 3, 5, null)<\/li>\n<\/ul>\n

        The first expression expands to (3 = 1) or<\/strong> (3 = 3) or<\/strong> (3 = 5) or<\/strong> (3 = null).<\/p>\n

        The second expression expands to (4 != 1) and<\/strong> (4 != 3) and<\/strong> (4 != 5) and<\/strong> (4 != null)<\/p>\n

        Because the first expression is a list of disjuncts (OR\u2019s<\/code>) it evaluates to TRUE<\/code> if any<\/em><\/strong> of the atomic expressions evaluates to TRUE<\/code>; in fact, at runtime Oracle will stop checking as soon as it finds the first expression that evaluates to TRUE<\/code> (i.e. 3 = 3<\/em>).<\/p>\n

        Because the second expression is a list of conjuncts (ANDs<\/code>), it evaluates to TRUE<\/code> only if every<\/em><\/strong> atomic expression evaluates to TRUE<\/code>. The first three expressions evaluate to TRUE<\/code>, but the last expression in the list is 4 != null<\/code>, which evaluates to UNKNOWN<\/code>, so the compound expression evaluates to FALSE<\/code>.<\/p>\n

        To the human eye, the value 4 is clearly not<\/strong> in the list<\/em>, but the Oracle database (or any other relational database) doesn\u2019t interpret NOT<\/code> IN<\/code> the way the human eye does.<\/p>\n

        This script will build the last example into a simple Oracle model:<\/p>\n

        create table t1 (n1 number);\r\ncreate table t2 (n2 number);\r\ninsert into t1 values(4);\r\ninsert into t2 values(1);\r\ninsert into t2 values(3);\r\ninsert into t2 values(5);\r\ninsert into t2 values(null);\r\ncommit;\r\nexecute dbms_stats.gather_table_stats(user,'t1')\r\nexecute dbms_stats.gather_table_stats(user,'t2')<\/pre>\n
        My table t1<\/em><\/strong> holds a row with the value 4, and my table t2 <\/em><\/strong>does not. I\u2019m going to start by querying t1<\/em><\/strong> for all the values that are IN table t2<\/em><\/strong>, then for all the values that are NOT<\/code> IN<\/code> table t2<\/em><\/strong> \u2013 here are the queries with results cut and pasted from an SQL*Plus session running 19.11:<\/p>\n
        SQL> select * from t1 where n1 in (select n2 from t2);\r\nno rows selected\r\nSQL> \r\nSQL> select * from t1 where n1 not in (select n2 from t2);\r\nno rows selected\r\nSQL><\/pre>\n
        Whether you ask for IN<\/code> or NOT<\/code> IN<\/code>, you get no rows \u2013 NOT<\/code> IN<\/code> is not<\/em><\/strong> the opposite of IN<\/code> if the subquery can return nulls. You can confirm this point by executing two more statements:<\/p>\n
        SQL> delete from t2 where n2 is null;\r\n1 row deleted.\r\nSQL> \r\nSQL> select * from t1 where n1 not in (select n2 from t2);\r\n        N1\r\n----------\r\n         4\r\n1 row selected.<\/pre>\n
        When deleting the null<\/em> row from t2,<\/em><\/strong> the not in<\/em> version of our query returns the 4 row.<\/p>\n
        Execution plans<\/h2>\n
        It\u2019s very easy to forget about the impact of nulls and the benefits of declaring mandatory columns as not null<\/em><\/strong>. Nulls introduce all sorts of problems (to people and to the optimizer). Over the years, the optimizer has been enhanced to work around some of the performance problems they used to produce.<\/p>\n
        Side note: Oracle handles not null<\/strong> column declarations differently from \u201c(column is not null)\u201d check constraints<\/strong>, and a not null<\/strong> declaration may allow the optimizer to consider extra execution paths that it would not otherwise be allowed to use. You may find that a simple not null<\/strong> declaration makes a huge difference to performance.<\/p>\n
        When looking at the execution plan from the first example with the NOT<\/code> IN<\/code> subquery, you can see Oracle giving a little warning about the presence of nulls and the impact it might have on our processing:<\/p>\n
        <\/p>\n
        The optimizer has chosen to use a hash anti-join<\/em><\/strong>. The significance of the ANTI<\/em><\/strong> is that the test fails if the search succeeds – the predicate for operation 1 must fail for every row in t2<\/em><\/strong> before the t1<\/em><\/strong> row can be reported. But there\u2019s a further label to the hash join operation: NA<\/em><\/strong>, for null-aware, which shows that Oracle is taking some sort of defensive action during the hash join to avoid making mistakes with nulls.<\/p>\n
        This suggests another way of understanding what\u2019s going on. You\u2019ve seen that IN<\/code> and NOT<\/code> IN<\/code> aren\u2019t exactly opposites, and you saw in the previous article that non-correlated IN subqueries could be turned into correlated EXISTS<\/code> subqueries. Add the null row back into t2<\/em><\/strong> and see what the execution plan looks like if you assume (incorrectly) that you can rewrite our non-correlated NOT<\/code> IN<\/code> subquery as a correlated NOT<\/code> EXISTS<\/code> subquery:<\/p>\n
        SQL> select * from t1 where not exists (select null from t2 where t2.n2 = t1.n1);\r\n        N1\r\n----------\r\n         4\r\n1 row selected.<\/pre>\n
        <\/p>\n
        The result from NOT<\/code> EXISTS<\/code> disagrees with the result from NOT IN, and the plan doesn\u2019t quite match the NOT<\/code> IN plan. Operation 1 (hash join anti) is not labeled as Null-aware<\/em> (NA), and there\u2019s a predicate at operation 3 that stops any null(s) from t2<\/em><\/strong> being passed up to the hash join operation. To sum up:<\/p>\n
          \n
        • NOT<\/code> EXISTS<\/code> is the opposite of EXISTS<\/code>,<\/li>\n
        • You can transform IN<\/code> to EXISTS<\/code><\/li>\n
        • You cannot (in the presence of nulls) transform NOT<\/code> IN to NOT<\/code> EXISTS<\/code><\/li>\n
        • NOT<\/code> IN is not (in the presence of nulls) the opposite of IN<\/code><\/li>\n<\/ul>\n
          You could get some idea of what is hidden behind the Null-Aware<\/em> tag by keeping an eye open for any surprises in plans, predicates, or execution statistics.<\/p>\n
          For example, change the two tables to increase the volume of data.<\/p>\n
          create table t1 (n1, v1) as\r\nselect \r\n        case when rownum <= 200 then rownum end n1, \r\n        lpad(rownum,100) v1\r\nfrom \r\n        all_objects\r\nwhere\r\n        rownum <= 50000\r\n;\r\ncreate table t2 as select n1 n2, v1 v2 from t1;\r\nset serveroutput off\r\nselect \/*+ gather_plan_statistics leading(t1 t2) *\/ \r\n        * \r\nfrom    t1 \r\nwhere   n1 not in (select n2 from t2)\r\n;\r\nselect  * from table(dbms_xplan.display_cursor(format=>'rowstats last'));<\/pre>\n
          I\u2019ve defined the two tables so that they both hold 50,000 rows, with only 200 rows with non-null values in each table. Here\u2019s the execution plan I get from my query \u2013 reported from memory after executing it with rowsource execution stats enabled:<\/p>\n
          <\/p>\n
          A key point to notice here is that the tablescan of t2<\/em><\/strong> stopped after only 201 rows \u2013 and a couple more tests setting other rows to null show that the scan stops as soon as Oracle finds a null in t2<\/em><\/strong>. This makes sense of course, because as soon as Oracle finds a null in t2, <\/em><\/strong>the query can\u2019t return any rows, so there\u2019s no point in continuing.<\/p>\n
          In passing, I was a little surprised that the actual row count (A-rows<\/em>) from t1<\/em><\/strong> was 50,000: I had assumed before I did the test that Oracle would only build the hash table from the rows where t1.n1<\/em><\/strong> was not null.<\/p>\n
          Alternative plans<\/h2>\n
          After understanding Oracle\u2019s handling of nulls and NOT IN, it\u2019s worth taking a quick note of a couple of variations in the patterns you might see in execution plans. These variations exist because of the null-aware<\/em> mechanisms as there are three possible variations with HASH JOIN ANTI operation. First, if the relevant column in the t2<\/em><\/strong> (i.e. subquery) table has been defined as not null (or if you add a column is not null predicate to the subquery, you get a single null aware SNA) join:<\/p>\n
          select \/*+ gather_plan_statistics leading(t1 t2) *\/  * from t1 where n1\r\nnot in (select n2 from t2 where n2 is not null)<\/pre>\n
          <\/p>\n
          Then, if you have not null declarations or predicates for the relevant columns from both tables the join becomes a normal anti join:<\/p>\n
          select \/*+ gather_plan_statistics leading(t1 t2) *\/  * from t1 where n1\r\nis not null and n1 not in (select n2 from t2 where n2 is not null)<\/pre>\n
          <\/p>\n
          Notice how the A-Rows<\/em><\/strong> supplied to the hash join by the t1<\/em><\/strong> tablescan is just the 200 where n1<\/em><\/strong> is not null in this case.<\/p>\n
          Another case which looks quite counter-intuitive appears if the statistics, indexing, and not null<\/em><\/strong> conditions are right:<\/p>\n
          create index t2_i2 on t2(n2);\r\nselect \/*+ gather_plan_statistics *\/  * from t1 where  n1 is not null\r\nand n1 not in (select n2 from t2)<\/pre>\n
          <\/p>\n
          I\u2019ve added a predicate to eliminate the null rows from t1<\/em><\/strong> and created an index on the n2<\/em><\/strong> column of t2<\/em><\/strong>, and that\u2019s had the effect of making the optimizer think that a nested loop join would be appropriate \u2013 though it is an anti join, of course, and also single null aware. However, if you look at the shape of the plan, you can see that the optimizer has started with a filter<\/em><\/strong> at operation 1, for which the first child is the nested loop at operation 2, and the second child is a full tablescan of t2<\/em><\/strong> at operation 5. Moreover, if you look at the Starts<\/em><\/strong> column in the plan, you\u2019ll see that operation 5 has started once, but operation 2 has never been started. This is an example of something I\u2019ve labeled the fixed subquery<\/em>, and it\u2019s an example of the rare case where the last child of a filter operation can be called first to decide whether or not the first child should execute at all.<\/p>\n
          In this case, operation 1 does convert the NOT<\/code> IN<\/code> subquery to a NOT<\/code> EXISTS<\/code> subquery (I\u2019ve expanded the predicate at operation 1 by passing the query through explain plan<\/em><\/strong> to show this), but this will only give the right answer if there are no rows with nulls for n2<\/em><\/strong> in table t2<\/em><\/strong>. So the run-time activity starts with the tablescan of t2<\/em><\/strong> at operation 5 to check whether or not there are any rows with n2 = null<\/em><\/strong>, and as you can see Oracle has found the first null, stopped the tablescan, and then not executed the nested loop.<\/p>\n
          Finally, I\u2019ll just point out that null-awareness<\/em> is not restricted to anti-joins \u2013 you may also see (S)NA on semi-joins, though the term Oracle uses, in this case, is null accepting<\/em> rather than null aware. (The feature appeared in 12.1, though I have published a note about a case that produced the wrong results until12.2). The SQL has to be a little more complex (and I\u2019ve hinted the following example fairly heavily to produce the plan I wanted from the 50,000 row tables of the previous examples):<\/p>\n
          select  \/*+ \r\n                gather_plan_statistics \r\n                leading(@sel$5da710d3 t1@sel$1 t2@sel$2)\r\n                use_nl(@sel$5da710d3 t2@sel$2)\r\n                index(@sel$5da710d3 t2@sel$2)\r\n        *\/\r\n        * \r\nfrom    t1 \r\nwhere\r\n        n1 is null\r\nor      exists (select null from t2 where n2 = n1)\r\n;<\/pre>\n
          <\/p>\n
          The effects of NULL on Oracle optimizer transformations<\/h1>\n
          You have seen IN subqueries converted to EXISTS<\/code> subqueries and learned that it is not automatically safe for Oracle (or the human programmer) to convert NOT<\/code> IN<\/code> subqueries to NOT<\/code> EXISTS<\/code> subqueries thanks to problems caused by nulls in the data. In recent versions of Oracle, the optimizer has been coded to work around some of the problems, but the side effects may produce plans that have to be less efficient than you might expect and plans that might have extra operations that seem to be redundant.<\/p>\n
          Join operations with the suffix NA<\/em><\/strong> (null-aware\/accepting<\/em>) or SNA<\/em><\/strong> (single null-aware\/accepting<\/em>) are clues that the plan has been adjusted to allow for columns that may hold nulls. Therefore, it could be worth checking whether it would be legal to add is not null<\/em> predicates to specific queries, or even to add NOT<\/code> NULL<\/code> declarations to relevant columns.<\/p>\n
           <\/p>\n","protected":false},"excerpt":{"rendered":"
          IN and NOT IN subqueries are frequently used in Oracle. Jonathan Lewis explains how the optimizer handles NULL with IN and NOT IN.…<\/p>\n","protected":false},"author":101205,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143533],"tags":[95506,4459],"coauthors":[39048],"class_list":["post-93390","post","type-post","status-publish","format-standard","hentry","category-featured","category-oracle-databases","tag-automate","tag-oracle"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93390","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/101205"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=93390"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93390\/revisions"}],"predecessor-version":[{"id":93571,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93390\/revisions\/93571"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=93390"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=93390"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=93390"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=93390"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}