Null Accepting Semi-Joins in 12c

Eine der neuen Optimierungstechniken, die im Query Optimizer 12c Einsatz finden, sind die sogenannten Null accepting semi-joins.

Einen Semi-Join sieht man bei „IN“ oder „EXISTS“-Subqueries, wenn der Optimizer als Transformation das sogenannte Subquery Unnesting durchgefügrt hat.

SQL> CREATE TABLE t1 (a NUMBER, c number);

Table created.

SQL>
SQL> CREATE TABLE t2 (b NUMBER, c number);

Table created.

SQL>
SQL> INSERT INTO t1 VALUES (1, 1);

1 row created.

SQL> INSERT INTO t1 VALUES (NULL, 2);

1 row created.

SQL>
SQL> INSERT INTO t2 VALUES (1, 1);

1 row created.

SQL> INSERT INTO t2 VALUES (NULL, 2);

1 row created.

SQL>
SQL> SELECT * FROM t1
  2  WHERE a IN (SELECT B FROM t2);

         A          C
---------- ----------
         1          1

SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

...
----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     6 (100)|
|*  1 |  HASH JOIN SEMI    |      |     1 |    39 |     6   (0)|
|   2 |   TABLE ACCESS FULL| T1   |     2 |    52 |     3   (0)|
|   3 |   TABLE ACCESS FULL| T2   |     2 |    26 |     3   (0)|
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"="B")

SQL>
SQL> SELECT * FROM t1
  2  WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.a=t2.b);

         A          C
---------- ----------
         1          1

SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

...
----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     6 (100)|
|*  1 |  HASH JOIN SEMI    |      |     1 |    39 |     6   (0)|
|   2 |   TABLE ACCESS FULL| T1   |     2 |    52 |     3   (0)|
|   3 |   TABLE ACCESS FULL| T2   |     2 |    26 |     3   (0)|
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."A"="T2"."B")

Soweit, keine Überraschungen hier. Beide Subqueries führen zu einem identischen Ausführungsplan mit HASH SEMI JOIN. Wie erwartet, wurde der Datensatz mit dem NULL-Wert in T1 nicht mitselektiert. Wenn man explizit auch Datensätze aus T1 selektieren möchte, wo die Spalte A leer ist, muss man eine extra Bedingung mit „OR“ verknüpfen:

SQL> ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='11.2.0.2';

Session altered.

SQL>
SQL> SELECT * FROM t1
  2  WHERE (EXISTS (SELECT 1 FROM t2 WHERE t1.a=t2.b)
  3         OR t1.a IS NULL);

         A          C
---------- ----------
         1          1
                    2

SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
...

-----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|
|*  1 |  FILTER            |      |       |       |            |
|   2 |   TABLE ACCESS FULL| T1   |     2 |    52 |     3   (0)|
|*  3 |   TABLE ACCESS FULL| T2   |     1 |    13 |     3   (0)|
-----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("T1"."A" IS NULL OR  IS NOT NULL))
   3 - filter("T2"."B"=:B1)

Leider stellt man vor 12c fest, dass Subquery Unnesting nicht mehr funktioniert. Hash Semi-Join ist nicht mehr möglich. Je nach Datenkonstellationen lässt die Performance der FILTER-Operation zu wünschen. Und das hat sich mit der Version 12c geändert!

SQL> ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='12.1.0.1';

Session altered.

SQL>
SQL> SELECT * FROM t1
  2  WHERE (EXISTS (SELECT 1 FROM t2 WHERE t1.a=t2.b)
  3         OR t1.a IS NULL);

         A          C
---------- ----------
         1          1
                    2

SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
...
----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     6 (100)|
|*  1 |  HASH JOIN SEMI NA |      |     1 |    39 |     6   (0)|
|   2 |   TABLE ACCESS FULL| T1   |     2 |    52 |     3   (0)|
|   3 |   TABLE ACCESS FULL| T2   |     2 |    26 |     3   (0)|
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."A"="T2"."B")

In der Zeile 22 sieht man im Ausführungsplan den entsprechenden Schritt: Null Accepting Semi Join. Bei der Ausführung wird erst geprüft, ob in der Join-Spalte in der linken Tabelle NULL-Werte vorkommen. Falls gefunden, werden solche Datensätze zurückgegeben. Mit dem Rest wird Semi-Join durchgeführt. Diese Vorgehensweise ist nicht auf den Hash-Join beschränkt: genauso sind Null accepting Nested Loops oder Merge Semi-Joins möglich.

SQL> SELECT  * FROM t1
  2  WHERE (EXISTS (SELECT /*+ NL_SJ */ 1 FROM t2 WHERE t1.a=t2.b)
  3         OR t1.a IS NULL);

         A          C
---------- ----------
         1          1
                    2

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
...
------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |       |       |     7 (100)|
|   1 |  NESTED LOOPS SEMI NA|      |     1 |    39 |     7   (0)|
|   2 |   TABLE ACCESS FULL  | T1   |     2 |    52 |     3   (0)|
|*  3 |   TABLE ACCESS FULL  | T2   |     1 |    13 |     2   (0)|
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T1"."A"="T2"."B")

Was ich nicht verstehen kann: Warum funktioniert es nur für Subqueries mit EXISTS? Anfangs haben wir gesehen, dass die IN-Subqueries intern zu EXISTS-Subqueries umgeschrieben werden und dafür exakt gleiche Ausführungspläne generiert werden. Nicht aber, wenn man sie mit NULL-Bedingung nach dem Beispiel von oben kombiniert:

SQL> SELECT  * FROM t1
  2  WHERE (t1.a IN (SELECT t2.b FROM t2)
  3         OR t1.a IS NULL);

         A          C
---------- ----------
         1          1
                    2

SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
...
----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|
|*  1 |  FILTER            |      |       |       |            |
|   2 |   TABLE ACCESS FULL| T1   |     2 |    52 |     3   (0)|
|*  3 |   TABLE ACCESS FULL| T2   |     1 |    13 |     3   (0)|
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("T1"."A" IS NULL OR  IS NOT NULL))
   3 - filter("T2"."B"=:B1)

Quelle:White Paper „Optimizer with Oracle Database 12c“

Ein Gedanke zu „Null Accepting Semi-Joins in 12c

  1. Martin Preiss

    „Warum funktioniert es nur für Subqueries mit EXISTS?“ – interessante Frage. Ich habe mal einen Blick auf das zugehörige CBO-Trace geworfen (12.1.0.1 unter Windows 7 64bit), finde das Ergebnis aber eher merkwürdig: für die EXISTS-Variante steht am Ende der Transformation die Query:

    SELECT "T1"."A" "A","T1"."C" "C" FROM "TEST"."T2" "T2","TEST"."T1" "T1" WHERE "T1"."A"="T2"."B"

    Also ein einfacher Join, bei dem die NULL-Prüfung komplett unter den Tisch gefallen zu sein scheint. Dabei ist das Ergebnis anscheinend das Ergebnis des Schritts „Cost-Based Subquery Unnesting“, was wiederum einleuchtet (wenn man das NULL-Prädikat mal außen vor lässt).

    Im Fall der Variante mit IN, erscheint im Schritt „Cost-Based Subquery Unnesting“ der Hinweis: „RSW: Not valid for subquery removal SEL$2 (#2)“, dafür werden dann allerlei andere Transformationen durchgeführt, an deren Ende Folgendes steht:

    SELECT "T1"."A" "A","T1"."C" "C" FROM "TEST"."T1" "T1" WHERE EXISTS (SELECT 0 FROM "TEST"."T2" "T2" WHERE "T2"."B"="T1"."A") OR "T1"."A" IS NULL

    Wenn ich da nichts Wesentliches übersehe, ist das exakt die Query, die in Fall 1 vor der Transformation steht. Meine Theorie wäre demnach, dass hier eine rekursive Transformation erforderlich wäre, die der CBO zumindest im Moment noch nicht in Betracht zieht.

    Nebenbei finde ich auch interessant, dass die Filter-Variante laut Costing eigentlich billiger sein sollte als der HASH JOIN SEMI NA, aber offenbar ist dem CBO in diesem Fall klar, dass er seiner eigenen Arithmetik nicht trauen kann …

    Antworten

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert