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
Preparing my session on Online Statistics Gathering for ETL for the DOAG conference, I noticed some points that I didn’t covered in the previous two blog posts. The first point is showing the problem that may arise if indexes are involved. The second one is about partition exchange load and it completes the topic of partitioned tables started in part 2. No blog posting on Oracle products is nowadays complete without mentioning the cloud. The third point is about Autonomous Data Warehouse Cloud Service and Online Statistics Gathering improvements. 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
It was not possible for me to write a follow-up to my last post about Transposing Columns To Rows with PTF showing an opposite task of transposing rows to columns right next weekend as I thought. Partly because of our awesome Trivadis TechEvent which took place back then and partly because this kind of the exercise turned out to be much more difficult one as supposed. Actually it is a nice example to see the limitations of the new feature. 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