{"id":395,"date":"2017-12-14T23:18:08","date_gmt":"2017-12-14T21:18:08","guid":{"rendered":"http:\/\/blog.sqlora.com\/de\/?p=395"},"modified":"2018-04-04T16:24:40","modified_gmt":"2018-04-04T14:24:40","slug":"polymorphic-table-functions","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/de\/polymorphic-table-functions\/","title":{"rendered":"Polymorphe Tabellenfunktionen"},"content":{"rendered":"

Letzten Monat konnte ich an der DOAG-Jahreskonferenz in N\u00fcrnberg teilnehmen. Wie immer ein tolles Event, gro\u00dfartige Community und exzellente Vortr\u00e4ge. Und es scheint so zu sein, als h\u00e4tte ich mein Lieblings-Feature der neuen Datenbank 18c gefunden. Keith Laker (@ASQLBarista<\/a>), Oracle’s Produkt Manager f\u00fcr Analytisches SQL, sparch \u00fcber „Building Agile Self-Describing SQL Functions For Big Data“. Der Vortragstitel war sehr vielversprechend und nat\u00fcrlich war ich nicht entt\u00e4uscht. Danke f\u00fcr sehr interessante Pr\u00e4sentation!<\/p>\n

Dieser Beitrag ist wohl etwas ungew\u00f6hnlich, weil ich noch kein echtes Know-How teilen kann, sondern erstmal meine Begeisterung \u00fcber die M\u00e4chtigkeit und Flexibilit\u00e4t von dem neuen Feature. Worum geht es bei dem Begriff „Polymorphic Table Functions“?<\/p>\n

Wahrscheinlich ist Ihnen bereits das Konzept von Tabellenfunktionen bekannt. Diese k\u00f6nnen „pipelined“ sein oder nicht, das ist nicht relevant. Wichtig ist, man kann aus so einer Funktion in SQL „selektieren“: <\/p>\n

\r\nselect * from table(dbms_xplan.display);\r\n<\/pre>\n
Die zweite wichtige Eigenschaft, ist dass die Tabellenfunktionen immer einen definierten Tabellen-Datentyp zur\u00fcckgeben. Zum Beispiel, liefert dbms_xplan.display<\/strong> den Typ dbms_xplan_type_table<\/strong> zur\u00fcck, der als TABLE OF VARCHAR2(4000) definiert ist. Es kann nicht passieren, dass die Logik in der Funktion dbms_xplan.display<\/strong> diesen Datentyp \u00e4ndert. Der Tabellen-Datentyp ist fix. Und das ist ein Hindernis, um flexible und generische Erweiterungsfunktionen implementieren zu k\u00f6nnen. <\/p>\n
Aber auch die Implementierung spezifischer Funktionen kann problematisch sein. Vielleicht haben Sie sich gefargt, wie gehe ich mit parallelen Ausf\u00fchrung um? Wenn Sie sich nach den M\u00f6glichkeiten umgeschaut haben, wie man Strings aggregieren (konkatenieren) kann, bevor die Funktion LISTAGG<\/strong> in 11g eingef\u00fchrt wurde, haben sie bestimmt schon mal die Tom Kyte’s Implementierung von der Funktion STRAGG<\/strong><\/a> gesehen. Nicht wirklich einfach, \u00fcber einen Objekttyp mit vier Methoden, unter anderem einer Methode, um die Teilergebnisse nach der parallelen Ausf\u00fchrung zu „mergen“.<\/p>\n
Polymorphe Tabellenfunktionen (PTF)<\/strong>  adressieren diese Probleme.  Polymorphe Tabellenfunktionen sind Teil von ANSI SQL 2016 Standard und sind eine Evolution der Tabellenfunktionen. Sie k\u00f6nnen in der FROM-Klausel aufgerufen werden, akzeptieren Daten aus beliebiger Tabellen (oder WITH-Unterabfragen). Der R\u00fcckgabe-Datentyp muss nicht bei Entwicklung festgelegt werden. Die Funktion kann die Daten verarbeiten, dabei Datens\u00e4tze hinzuf\u00fcgen oder weggruppieren, Spalten hinzuf\u00fcgen oder herausnehmen, und die verarbeiteten Daten weiter aus der Funktion an die SQL-Abfrage zur\u00fcckgeben. Der Datentyp der zur\u00fcckgegebener Tabelle muss nicht vorher definiert werden.<\/p>\n
Ich verzichte auf mein eigenes Beispiel, weil es eine Spekulation w\u00e4re, ohne es selber in einer Datenbank ausprobiert zu haben. Hier ist das Beispiel aus dem Vortrag von Keith Laker: Kredit-Score und Risiko-Level eines Kunden zur\u00fcckgeben:<\/p>\n
\"\"<\/a><\/p>\n
Wir \u00fcbergeben die Tabelle scott.customers<\/strong> in die Funktion CREDIT_RISK, definieren die Spalten, die man f\u00fcr Berechnungen braucht und die Spalten, die zur\u00fcckgegeben werden m\u00fcssen. Eigentlich bin ich der Meinung, dass wir hier noch die Spalte STATE_ID im Parameter OUTS erw\u00e4hnen sollten, damit wir danach gruppieren k\u00f6nnen. Kann aber sein, dass ich etwas missverstanden habe. Das war’s. Wir k\u00f6nnen aus der Funktion einfach selektieren: die neu berechneten Spalten sind an jedem Datensatz dran und k\u00f6nnen gruppiert werden.<\/p>\n
Mann kann „Leaf“ und „Non-Leaf“ PTFs unterscheiden und die letzten k\u00f6nnen row semantic<\/strong> (RS PTF) oder table semantic<\/strong> (TS PTF) sein. Der Unterschied ist, dass man bei  row semantic nur den aktuellen Datensatz braucht, um alle Outputs zu produzieren. Bei table semantic muss man auf die vorher verarbeiteten Datens\u00e4tze schauen, um neue Spalten oder Datens\u00e4tze zu generieren – hier geht es eher um eine Art Aggregat-\/analytische Funktionen. Mit TS PTF kann man optional den Input partitionieren und ordnen (wie bei analytischen Funktionen):<\/p>\n
\r\nSELECT *\r\nFROM   CUSTOM_GRPBY(scott.emp PARTITION BY job ORDER BY empno);\r\n<\/pre>\n
Wie sieht es mit parallelen Ausf\u00fchrung aus? Die Aussage ist, der Entwickler kann einfach von einer seriellen Ausf\u00fchrung ausgehen. Alles andere wird durch die Datenbank geregelt. Mit einer Anmerkung: bei Tabellensemantik muss man die Partitionierung mit angeben, wie im Beispiel oben. Alle Datens\u00e4tze einer Partition landen auf demselben Parallel Slave. Gibt man die Partitionierung nicht an, wird die Funktion zu einem Serialisierungspunkt.<\/p>\n
Es gibt auch ein paar gute Neuigkeiten f\u00fcr Performance: Bulk-\u00dcbertragung der Daten in die und aus der Funktion, wobei nur die ben\u00f6tigten Spalten \u00fcbertragen werden. Soweit m\u00f6glich, werden die Pr\u00e4dikate, Partitionierung in die darunterliegende Tabelle weitergereicht.<\/p>\n
Alle Datentypen und Hilfsroutinen zum Implementieren der PTFs sind im Package DBMS_TF<\/strong> zu finden. \u00dcbrigens, das Package ist bereits in der Version 12.2.0.1 da, wenn auch (noch) nicht dokumentiert. Sie k\u00f6nnen sogar versuchen, ihre erste PTF anzulegen, es geht aber nicht ganz. Schlie\u00dflich ist es ein 18c Feature!<\/p>\n
\"\"<\/a><\/p>\n
\r\ncreate or replace package ptf_test as \r\n  procedure describe (new_cols out dbms_tf.columns_new_t,  tab in out dbms_tf.table_t, cols dbms_tf.columns_t) ;\r\n  function my_ptf (tab table, type_name varchar2, flip varchar2 default 'True') \r\n       return table pipelined row polymorphic using ptf_test;\r\nend;\r\n\/\r\n\r\nshow errors\r\n\r\nLINE\/COL ERROR\r\n-------- --------------------------------------------------------\r\n3\/124    PLS-00998: implementation restriction (may be temporary)\r\n\r\n<\/pre>\n
Welche interessante Anwendungsf\u00e4lle kann ich mir vorstellen? Im Moment arbeite ich an der Integration der Daten in einem Data Warehouse System, die urspr\u00fcnglich aus einem XBRL<\/a> Format stammen. In der Quelldatenbank werden diese XBRL-Daten in einer sog. „Key-Value“ Tabelle persistiert. Nun sind die Anforderungen, wie man mit DAtens\u00e4tzen aus der riesigen Key-Value-Tabelle umgeht, unterschiedlich, meistens abh\u00e4ngig von den Key-Werten und sind SQL-technisch nicht „kompatibel“. Mal muss man diese aggregieren, mal pivotieren auch mit Erkennung von sog. „Tupeln“, mal noch was anderes. Dies macht mehrfaches Scannen der gro\u00dfen Tabelle n\u00f6tig. Was w\u00e4re, wenn wir die M\u00f6glichkeit h\u00e4tten, die Tabelle nur einmal zu scannen und dann innerhalb einer PTF die Datens\u00e4tze bedingt aggregieren, pivotieren, was auch immer? Man k\u00f6nnte einen ETL-Prozess entwickeln, der aus einer gro\u00dfen Tabelle liest, reicht die Daten in die PTF weiter, die unterschiedliche Datenstr\u00f6me vorbereitet, und leitet die anschlie\u00dfend \u00fcber INSERT ALL SQL Statement in mehrere Zieltabellen weiter. Alles in einem Schritt. Wenn das auch schnell l\u00e4uft, warum nicht? <\/p>\n
Apropos Pivot, wird es wohl auch m\u00f6glich sein, eine Funktion f\u00fcr dynamisches PIVOT zu entwickeln, wobei man nicht zur Entwicklungszeit festlegen muss, welche Werte zu Spalten werden, \u00e4hnlich wie die von Anton Scheffer<\/a>, aber ohne, dass man den Abfragetext \u00fcbergeben muss? Vielleicht habe ich noch was \u00fcbersehen, aber erstmal es klingt gut f\u00fcr mich.<\/p>\n
Wenn alles so funktioniert wie versprochen und auch schnell und skalierbar ist, wird es ein sehr interessantes Feature! Abwarten! Bleiben Sie dran, ich werde definitiv im n\u00e4chsten Jahr \u00fcber Polymorphische Funktionen schreiben und ich kann es kaum erwarten, an den Beispielen und Folien zu diesem Thema f\u00fcr unseren Kurs New Features Training<\/a> zu arbeiten.<\/p>\n
Related Posts<\/h3>