Category Archives: 26ai

Oracle 26ai: QUALIFY Under the Hood #JoelKallmanDay

Leave a reply

Analytic functions have been among my favorite Oracle SQL features ever since their introduction in Oracle 8i. I’ve used them extensively over the years, and I still remember how much easier they made many SQL problems that previously required rather complicated solutions.

Tom Kyte once described analytic functions as “the coolest thing to happen to the SQL language since the introduction of the keyword SELECT.” I couldn’t agree more.

But there was one thing I kept missing: the ability to filter directly on the results of analytic functions. With the new QUALIFY clause in Oracle 26ai, this is finally possible. It’s a relatively small addition to Oracle SQL, but a long-awaited and very welcome one.

Let’s see how it works – and then take a look at something rather surprising under the hood.

Continue reading →

Oracle 26ai: An Even Closer Look at JOIN TO ONE

2 Replies

Sometimes the most interesting findings start with a simple question. After publishing my recent post on Oracle 26ai JOIN TO ONE, Iudith Mentzel pointed out something in one of the query transformations that looked perfectly logical – but turned out not to describe what Oracle actually does.

A small test led me one level deeper, from DBMS_UTILITY.EXPAND_SQL_TEXT to the CBO trace, and revealed another interesting aspect of JOIN TO ONE: the way Oracle handles its implicit LEFT OUTER JOIN.

Continue reading →

Oracle 26ai: A Closer Look at JOIN TO ONE

3 Replies

With Oracle AI Database 23.26.2, Oracle introduced the new JOIN TO ONE syntax. As it happened, just a few weeks later, I was teaching an Oracle 26ai course for data warehouse developers, and we actually managed to take a first look at this brand-new feature. We found it interesting and promising – but it also immediately raised some critical questions. Our conclusion was clear: this deserves a closer look. And that is exactly what this blog post is about.

Continue reading →