Monat: Februar 2018

Lösung: Unpivot mit Cross Apply

Die cross apply erzeugt so etwas wie ein cartesisches Produkt. Zudem wird die Regel aufgehoben, dass alle Tabellen in der FROM Klausel gleichzeit sind.

Es wird eine Reihenfolge definert und es ist eine Referenz innerhaln der FROM Klausel möglich.

Das Statement unten kombiniert jeden Datensatz der Basistabelle emp mit allen Sätzen der brechneten Tabelle CrossApplied. Das Verblüffende ist, CrossApplied sich auch auf Werte in emp beziehen kann.

Der execution Plan dazu ist unten aufgeführt. Man sieht die Verwandtschaft von CROSS APPLY zur ebenfalls neuen Klause LATERAL.

Tatsächlich war diese Variante im einem Test mit 14 Millionen Sätzen etwas schneller als die Lösung mit UNPIVOT Klausel.

SELECT  empno,
        CrossApplied.Col_name, CrossApplied.Col_value
  FROM emp t
 CROSS APPLY (Select 'Mgr' col_name, t.Mgr col_value from dual
              UNION ALL
              Select 'job', t.job  from dual
              UNION ALL
              Select 'sal', t.sal  from dual) CrossApplied
;


 --------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |       |       |    87 (100)|          |
|   1 |  NESTED LOOPS      |                 |    42 |  1386 |    87   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP             |    14 |   210 |     3   (0)| 00:00:01 |
|   3 |   VIEW             | VW_LAT_C2E3294A |     3 |    54 |     6   (0)| 00:00:01 |
|   4 |    UNION-ALL       |                 |       |       |            |          |
|   5 |     FAST DUAL      |                 |     1 |       |     2   (0)| 00:00:01 |
|   6 |     FAST DUAL      |                 |     1 |       |     2   (0)| 00:00:01 |
|   7 |     FAST DUAL      |                 |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Advertisements

Unpivot mit CROSS_APPLY

Manchmal wird in einer Verarbeitungslogik ein unpivot benötigt. Beispielsweise wenn eine generische Schnittstelle befüllt werden soll. Üblicherweise enthält eine solche Schnittstellentabelle Schlüsselfelder, einen Spaltennamen und einen Spaltenwert.

Ich bin kein Freund solcher Schnittstellentabellen, aber man hat nicht immer die Wahl. 😉 Bei der Entwicklung einer zeitkritischen Verarbeitung war das Standard Unpivot mir zu langsam und ich suchte Alternativen.

Dabei stiess ich auf die relative neue cross_apply klausel und fand es noch interessant sie näher kennen zu lernen. Das unpivot Beispiel eignet sich dafür noch recht gut.

Diese Aufgabe will ich ihnen daher nicht vorenthalten.

Also: Wenn das meine Unpivot query ist, wie kann man mit cross_apply zum selben Resultat kommen?


SELECT  empno, column_name, value
FROM emp t
UNPIVOT ( VALUE FOR COLUMN_NAME IN ( Mgr, deptno, sal))
/