LiveSQL is great place to start playing with new features. It provides a couple of very helpful demo scripts explaining how polymorphic table functions work. There I found a new script few days ago which uses PTF for dynamic pivot! WOW! According to my subjective perception, it seems to be one of the most desired features in Oracle SQL! But let’s have a closer look. Is this really feasible and mature enough to be used in production code? Weiterlesen
In the first three parts of the series we have seen how a PTF basically works, but we have focused on row-semantic PTF only. In this part we’ll look at the table semantic PTF. As a reminder, the main difference to row-semantic PTF is that it is not enough to look at the currently processed row to produce the result. With table semantic PTF we often also need some summarized state from the previously processed rows. They are useful to implement user defined aggregate or window functions. Let’s first try to implement a very basic example of table semantic PTF and learn more theory as we go. Weiterlesen
In the third part of the PTF-series we learn how a PTF can change the cardinality of the input data flow: return more or less rows as in the input. We’ll use the same simple table as in the part 2 and our new task will be column transposing. We’ll still define, which columns have to stay unchanged (as we already did using the parameter cols2stay). All other columns should be displayed as key-value pairs. Weiterlesen
In the first part of PTF series we went through a very basic example removing some columns and adding a new column with a constant value. Starting from the same example we’ll do something more meaningful in the second part. How about concatenating the values of the removed columns as CSV in a new column? Weiterlesen
Writing the second „basics“ post on PTF I discovered, that there were much more details worth mentioning, than it would be acceptable for a „basics“ post and would blow it up anyway 😉 So I decided to to separate the tests and finding in this (more deep-dive) post. Weiterlesen
I have already posted some examples on Polymorphic Table Functions in Oracle 18c in the last months. I quickly realized how difficult it is to explain completely new feature using advanced examples and wanted to write a series of posts starting from very basics. Now that the Germany’s Oracle User Group (DOAG) has accepted my presentation on PTF for their annual conference is the time to do it. Weiterlesen
Letzten Monat konnte ich an der DOAG-Jahreskonferenz in Nürnberg teilnehmen. Wie immer ein tolles Event, großartige Community und exzellente Vorträge. Und es scheint so zu sein, als hätte ich mein Lieblings-Feature der neuen Datenbank 18c gefunden. Keith Laker (@ASQLBarista), Oracle’s Produkt Manager für Analytisches SQL, sparch über „Building Agile Self-Describing SQL Functions For Big Data“. Der Vortragstitel war sehr vielversprechend und natürlich war ich nicht enttäuscht. Danke für sehr interessante Präsentation!
Dieser Beitrag ist wohl etwas ungewöhnlich, weil ich noch kein echtes Know-How teilen kann, sondern erstmal meine Begeisterung über die Mächtigkeit und Flexibilität von dem neuen Feature. Worum geht es bei dem Begriff „Polymorphic Table Functions“? Weiterlesen