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.
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.
In the previous post, we explored the new CALENDAR functions introduced in Oracle 26ai — their syntax, strengths, and some subtle caveats. Now it’s time to look at the architectural question: if date logic has become easier to express directly in SQL, does that reduce the need for a dedicated time dimension in analytical models?
Continue reading
Oracle AI Database 26ai (23.26.1) introduces new CALENDAR SQL functions. Having written countless nested TRUNC/ADD_MONTHS/TO_CHAR/LAST_DAY expressions over the years, I expected to love them immediately. They do simplify many scenarios — yet they also introduce nuances that require careful understanding before using them everywhere.
Continue reading
This is not just another post about why correct data types matter. Most of you know that using the wrong data type in WHERE or JOIN conditions can trigger implicit conversions, prevent an index access path, and cause performance problems. But what if you can’t change the SQL statements, and you don’t want to redesign your data model? This post is about possible solutions in case of a wrong cardinality estimation due to an implicit data type conversion.
Continue reading
For a long time, we all “knew” that MERGE did not support a RETURNING clause. You can ask ChatGPT, Gemini, or Google, and the answer would still be the same: Nope, no luck! LLMs respond with confidence, and Google’s top results show various workarounds for the problem. I swear I saw the lifting of this restriction in the New Features Guide back when the version was still called 23c :-). Since I’m currently preparing a conference talk where this is relevant, I naturally wanted to check whether my memory was just playing tricks on me — or if it’s actually true.
Continue reading
SQL macros and WITH clause are not known to be great friends: you cannot call a SQL macro in a WITH clause and if you want to define a table macro returning a query containing a WITH subquery(ies), then you won’t be able to use scalar parameters in this subquery. In the previous post I tried to explain another restriction from the technical perspective looking at the SQL macro expansion trace information. In this post I will try to understand the WITH clause related limitations using the same approach.
Continue reading
In the previous post I showed how to get to the final query after SQL macro expansion using CBO trace. But there are still a few interesting things in the trace file that are worth looking at.
Continue reading
In my very first post about SQL macros, I mentioned that for table macros, there is a simple way to see the SQL statement after macro expansion using dbms_utility.expand_sql_text. However, for scalar SQL macros, there is no such straightforward method. We can activate a CBO trace (also known as event 10053) and find the final statement in the trace file. This approach works for both scalar and table SQL macros. In this post, we will explore how to do this, and we will use… a SQL macro for that! Well, at least we will give it a try…
I had to learn from this mistake more than once in my SQL career, until it became a kind of reflex: always think about the NULL values when you are writing a NOT IN subquery. Well, you should ALWAYS keep the NULL values in mind as a SQL developer, but in this case the consequences can be really nasty! And it drives me crazy how many times I’ve seen this error in my consulting life, even in production code. So, what is the point here?
Continue reading