{"id":159,"date":"2013-07-13T15:04:29","date_gmt":"2013-07-13T13:04:29","guid":{"rendered":"http:\/\/blog.sqlora.com\/de\/?p=159"},"modified":"2013-11-16T19:10:33","modified_gmt":"2013-11-16T17:10:33","slug":"null-accepting-semi-joins-in-12c","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/de\/null-accepting-semi-joins-in-12c\/","title":{"rendered":"Null Accepting Semi-Joins in 12c"},"content":{"rendered":"

Eine der neuen Optimierungstechniken, die im Query Optimizer 12c Einsatz finden, sind die sogenannten Null accepting semi-joins. <\/p>\n

Einen Semi-Join sieht man bei „IN“ oder „EXISTS“-Subqueries, wenn der Optimizer als Transformation das sogenannte Subquery Unnesting durchgef\u00fcgrt hat.<\/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
Soweit, keine \u00dcberraschungen hier. Beide Subqueries f\u00fchren zu einem identischen Ausf\u00fchrungsplan mit HASH SEMI JOIN. Wie erwartet, wurde der Datensatz mit dem NULL-Wert in T1 nicht mitselektiert. Wenn man explizit auch Datens\u00e4tze aus T1 selektieren m\u00f6chte, wo die Spalte A leer ist, muss man eine extra Bedingung mit „OR“ verkn\u00fcpfen:<\/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
Leider stellt man vor 12c fest, dass Subquery Unnesting nicht mehr funktioniert. Hash Semi-Join ist nicht mehr m\u00f6glich. Je nach Datenkonstellationen l\u00e4sst die Performance der FILTER-Operation zu w\u00fcnschen.  Und das hat sich mit der Version 12c ge\u00e4ndert!<\/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
In der Zeile 22 sieht man im Ausf\u00fchrungsplan den entsprechenden Schritt: Null Accepting Semi Join. Bei der Ausf\u00fchrung wird erst gepr\u00fcft, ob in der Join-Spalte in der linken Tabelle NULL-Werte vorkommen. Falls gefunden, werden solche Datens\u00e4tze zur\u00fcckgegeben. Mit dem Rest wird Semi-Join durchgef\u00fchrt. Diese Vorgehensweise ist nicht auf den Hash-Join beschr\u00e4nkt: genauso sind Null accepting Nested Loops oder Merge Semi-Joins m\u00f6glich.<\/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
Was ich nicht verstehen kann: Warum funktioniert es nur f\u00fcr Subqueries mit EXISTS? Anfangs haben wir gesehen, dass die IN-Subqueries intern zu EXISTS-Subqueries umgeschrieben werden und daf\u00fcr exakt gleiche Ausf\u00fchrungspl\u00e4ne generiert werden. Nicht aber, wenn man sie mit NULL-Bedingung nach dem Beispiel von oben kombiniert:<\/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
Quelle:White Paper „Optimizer with Oracle Database 12c“<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Eine der neuen Optimierungstechniken, die im Query Optimizer 12c Einsatz finden, sind die sogenannten Null accepting semi-joins.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17,11,3,4,22],"tags":[20,21,19,13,18,23],"class_list":["post-159","post","type-post","status-publish","format-standard","hentry","category-12c","category-cbo","category-oracle","category-sql","category-trivadis","tag-null-accepting","tag-optimizer","tag-semi-join","tag-subquery","tag-tc12c","tag-trivadiscontent"],"_links":{"self":[{"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/posts\/159","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/comments?post=159"}],"version-history":[{"count":24,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/posts\/159\/revisions"}],"predecessor-version":[{"id":206,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/posts\/159\/revisions\/206"}],"wp:attachment":[{"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/media?parent=159"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/categories?post=159"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/tags?post=159"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}