I’m neither an Oracle DBA, nor am I a Docker expert. How on earth did I get here to write this post? Well, I’m still an Oracle Developer and as you may have noticed, I was recently focused on SQL macros. It is a new feature of Oracle Database 20c, but obviously so awesome that it was backported to 19.6.0. Of course I asked myself, how can I start testing SQL macros in 19c?
Continue reading
More than a year ago I blogged about my view on using Polymorphic Table Function (PTF) for dynamic pivot in Oracle 18c. Actually I was not as optimistic. The solution had at least two significant problems:
With Oracle 20c we now have SQL macros and I was curious, whether they can help here.
Continue reading
In a previous post on SQL macros in Oracle Database 20c we saw how SQL macros can be used to create a kind of “parameterized” views to establish a simplified access tier to temporal data. In this post I’d like to explore more possibilities to hide the complexity of SQL statements behind a functional syntax provided by using SQL macros. As an example we’ll stay with a temporal data introduced in the previous post and explore how we can do a temporal join of this versioned data.
Continue reading
Views have always been an efficient tool for encapsulating complex logic, creating defined access structures and so on. But there is one thing views cannot do: accept parameters. And this is unfortunately a big disadvantage in terms of flexibility. As a result, there are a number of workarounds, none of them without their drawbacks.
Continue reading
In the next post about SQL macros in Oracle 20c we look at how they could be useful for building hash keys. If you are familiar with Data Warehousing and Data Vault modelling approach, you will probably know why it can be a good idea to build hash keys or hash diffs. Anyway, we will not discuss whether or not you should use them, but rather how you can do this in Oracle in a consistent and performant way.
Continue reading
In this part we will keep focus on scalar macros going into more detail and look at parameter passing and parsing.
Continue reading
The preview version of Oracle 20c is now available in the cloud and I got the chance to test some new features there. I start with SQL macros, a feature that I think could well become a kind of game changer: let’s just look at the long-awaited parameterized views.
Continue readingPerforming 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 has 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!
Continue reading
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?
Continue reading
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. Continue reading