Archiv der Kategorie: Oracle

Issue with the Hint ENABLE_PARALLEL_DML

Schreibe eine Antwort

Performing an ETL with large data sets, it is often a good idea to run DML in parallel. But, in contrast to parallel query or DDL, parallel DML have to be explicitly enabled. You had to issue ALTER SESSION ENABLE PARALLEL DML in the past. Starting with 12c you can enable parallel DML specifically for each query using the hint ENABLE_PARALLEL_DML. For a few years now, I’ve been using the hint now and then and was quite happy. An observation I made a few days ago can lead to a rethinking. What I could observe is that for the SQL with embedded hint a new child cursor was created each time. Let’s test it! Weiterlesen →

Dynamic Pivot with Polymorphic Table Function?

Schreibe eine Antwort

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 →

Polymorphic Table Functions (PTF), Part 4 – Table Semantic PTF

Schreibe eine Antwort

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 →

Polymorphic Table Functions (PTF) , Part 3 – Row Replication

Schreibe eine Antwort

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 →

Online Statistics Gathering for ETL – Part 3

Schreibe eine Antwort

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 →

Polymorphic Table Functions (PTF) , Part 2 – More Basics With Some Deep-Dive

Schreibe eine Antwort

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 →

Polymorphic Table Functions (PTF) – Tinkering with Rowsets

Schreibe eine Antwort

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 →

Polymorphic Table Functions (PTF) , Part 1 – Basics

Schreibe eine Antwort

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 →