{"id":101,"date":"2013-07-13T15:03:22","date_gmt":"2013-07-13T13:03:22","guid":{"rendered":"http:\/\/blog.sqlora.com\/en\/?p=101"},"modified":"2013-10-22T16:49:03","modified_gmt":"2013-10-22T14:49:03","slug":"null-accepting-semi-joins-in-12c","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/en\/null-accepting-semi-joins-in-12c\/","title":{"rendered":"Null Accepting Semi-Joins in 12c"},"content":{"rendered":"

One of the new query optimizations in 12c are the null-accepting semi-joins. They make subquery unnesting and a semi-join possible in a situation which led to FILTER operation in older versions. <\/p>\n

One can see a semi-join with “IN” oder “EXISTS” subqueries, if the optimizer choosed to unnest the subquery.<\/p>\n

\r\nSQL> CREATE TABLE t1 (a NUMBER, c number);\r\n\r\nTable created.\r\n\r\nSQL>\r\nSQL> CREATE TABLE t2 (b NUMBER, c number);\r\n\r\nTable created.\r\n\r\nSQL>\r\nSQL> INSERT INTO t1 VALUES (1, 1);\r\n\r\n1 row created.\r\n\r\nSQL> INSERT INTO t1 VALUES (NULL, 2);\r\n\r\n1 row created.\r\n\r\nSQL>\r\nSQL> INSERT INTO t2 VALUES (1, 1);\r\n\r\n1 row created.\r\n\r\nSQL> INSERT INTO t2 VALUES (NULL, 2);\r\n\r\n1 row created.\r\n\r\nSQL>\r\nSQL> SELECT * FROM t1\r\n  2  WHERE a IN (SELECT B FROM t2);\r\n\r\n         A          C\r\n---------- ----------\r\n         1          1\r\n\r\nSQL>\r\nSQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);\r\n\r\n...\r\n----------------------------------------------------------------\r\n| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|\r\n----------------------------------------------------------------\r\n|   0 | SELECT STATEMENT   |      |       |       |     6 (100)|\r\n|*  1 |  HASH JOIN SEMI    |      |     1 |    39 |     6   (0)|\r\n|   2 |   TABLE ACCESS FULL| T1   |     2 |    52 |     3   (0)|\r\n|   3 |   TABLE ACCESS FULL| T2   |     2 |    26 |     3   (0)|\r\n----------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n   1 - access("A"="B")\r\n\r\nSQL>\r\nSQL> SELECT * FROM t1\r\n  2  WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.a=t2.b);\r\n\r\n         A          C\r\n---------- ----------\r\n         1          1\r\n\r\nSQL>\r\nSQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);\r\n\r\n...\r\n----------------------------------------------------------------\r\n| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|\r\n----------------------------------------------------------------\r\n|   0 | SELECT STATEMENT   |      |       |       |     6 (100)|\r\n|*  1 |  HASH JOIN SEMI    |      |     1 |    39 |     6   (0)|\r\n|   2 |   TABLE ACCESS FULL| T1   |     2 |    52 |     3   (0)|\r\n|   3 |   TABLE ACCESS FULL| T2   |     2 |    26 |     3   (0)|\r\n----------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n   1 - access("T1"."A"="T2"."B")\r\n\r\n<\/pre>\n
Nothing really surprising here. Both subqueries lead to identical execution plans with HASH SEMI JOIN. As expected, the record with NULL-value in T1 was not selected. If one explicitly wants to select the records from T1 where column value of A is NULL, it is possible to do so defining extra WHERE clause with OR:<\/p>\n
\r\nSQL> ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='11.2.0.2';\r\n\r\nSession altered.\r\n\r\nSQL>\r\nSQL> SELECT * FROM t1\r\n  2  WHERE (EXISTS (SELECT 1 FROM t2 WHERE t1.a=t2.b)\r\n  3         OR t1.a IS NULL);\r\n\r\n         A          C\r\n---------- ----------\r\n         1          1\r\n                    2\r\n\r\nSQL>\r\nSQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);\r\n...\r\n\r\n-----------------------------------------------------------------\r\n| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|\r\n-----------------------------------------------------------------\r\n|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|\r\n|*  1 |  FILTER            |      |       |       |            |\r\n|   2 |   TABLE ACCESS FULL| T1   |     2 |    52 |     3   (0)|\r\n|*  3 |   TABLE ACCESS FULL| T2   |     1 |    13 |     3   (0)|\r\n-----------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n   1 - filter(("T1"."A" IS NULL OR  IS NOT NULL))\r\n   3 - filter("T2"."B"=:B1)\r\n<\/pre>\n
But before 12c we could see that subquery unnesting was not possible anymore. Hence, no hash semi-join but rather a FILTER-Operation, where our subquery will be evaluated once per row. Needless to say, this is not fast enough. And this is what was changed in 12c!<\/p>\n
\r\nSQL> ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='12.1.0.1';\r\n\r\nSession altered.\r\n\r\nSQL>\r\nSQL> SELECT * FROM t1\r\n  2  WHERE (EXISTS (SELECT 1 FROM t2 WHERE t1.a=t2.b)\r\n  3         OR t1.a IS NULL);\r\n\r\n         A          C\r\n---------- ----------\r\n         1          1\r\n                    2\r\n\r\nSQL>\r\nSQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);\r\n...\r\n----------------------------------------------------------------\r\n| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|\r\n----------------------------------------------------------------\r\n|   0 | SELECT STATEMENT   |      |       |       |     6 (100)|\r\n|*  1 |  HASH JOIN SEMI NA |      |     1 |    39 |     6   (0)|\r\n|   2 |   TABLE ACCESS FULL| T1   |     2 |    52 |     3   (0)|\r\n|   3 |   TABLE ACCESS FULL| T2   |     2 |    26 |     3   (0)|\r\n----------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n   1 - access("T1"."A"="T2"."B")\r\n<\/pre>\n
You can see the corresponding step of the execution plan on line 22: Null Accepting Semi Join. First, a check would be done, whether null values are there in the join column of the left table. The corresponding rows will be returned if found. Then the semi-join will be performed with the rest of the rows. This approach is not limited to Hash-Join: null-accepting Nested Loops or Merge Semi-Joins are also possible.<\/p>\n
\r\nSQL> SELECT  * FROM t1\r\n  2  WHERE (EXISTS (SELECT \/*+ NL_SJ *\/ 1 FROM t2 WHERE t1.a=t2.b)\r\n  3         OR t1.a IS NULL);\r\n\r\n         A          C\r\n---------- ----------\r\n         1          1\r\n                    2\r\n\r\nSQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);\r\n...\r\n------------------------------------------------------------------\r\n| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)|\r\n------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT     |      |       |       |     7 (100)|\r\n|   1 |  NESTED LOOPS SEMI NA|      |     1 |    39 |     7   (0)|\r\n|   2 |   TABLE ACCESS FULL  | T1   |     2 |    52 |     3   (0)|\r\n|*  3 |   TABLE ACCESS FULL  | T2   |     1 |    13 |     2   (0)|\r\n------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n   3 - filter("T1"."A"="T2"."B")\r\n<\/pre>\n
But what I don’t understand yet: why is it only for subqueries with EXISTS? We’ve seen that IN subqueries are being transformed to EXISTS correlated subqueries resulting in identical execution plans with semi-joins. But not if we combine the subquery with IS NULL clause:<\/p>\n
\r\nSQL> SELECT  * FROM t1\r\n  2  WHERE (t1.a IN (SELECT t2.b FROM t2)\r\n  3         OR t1.a IS NULL);\r\n\r\n         A          C\r\n---------- ----------\r\n         1          1\r\n                    2\r\n\r\nSQL>\r\nSQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);\r\n...\r\n----------------------------------------------------------------\r\n| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|\r\n----------------------------------------------------------------\r\n|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|\r\n|*  1 |  FILTER            |      |       |       |            |\r\n|   2 |   TABLE ACCESS FULL| T1   |     2 |    52 |     3   (0)|\r\n|*  3 |   TABLE ACCESS FULL| T2   |     1 |    13 |     3   (0)|\r\n----------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n   1 - filter(("T1"."A" IS NULL OR  IS NOT NULL))\r\n   3 - filter("T2"."B"=:B1)\r\n<\/pre>\n
Source: White Paper “Optimizer with Oracle Database 12c”<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
One of the new query optimizations in 12c are the null-accepting semi-joins. They make subquery unnesting and a semi-join possible in a situation which led to FILTER operation in older versions.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,10,2,3],"tags":[52,12,11,13,15],"class_list":["post-101","post","type-post","status-publish","format-standard","hentry","category-12c","category-cbo","category-oracle","category-sql","tag-12c","tag-null-accepting","tag-optimizer","tag-subquery","tag-tc12c"],"_links":{"self":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/101","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/comments?post=101"}],"version-history":[{"count":15,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/101\/revisions"}],"predecessor-version":[{"id":129,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/101\/revisions\/129"}],"wp:attachment":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/media?parent=101"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/categories?post=101"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/tags?post=101"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}