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 readingTag Archives: SQL
SQL Macros – what is in the CBO trace?
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 readingHow to get the final SQL after macro expansion
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…
Null Values? NOT IN my Subquery! #JoelKallmanDay
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 readingNot Every “Not Equals” is Equal
Yesterday I voted on the poll by @SQL Daily about using different forms for “not equals” conditions and that brought back bad (and funny) memories. Over 25 years I have always used “!=”. “Does it matter”, you might ask? Actually, it shouldn’t. But here is my story and the reason, why I was considering to change my habits and use “<>”.
Continue readingOracle 20c: SQL Macros
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 readingSubquery Unnesting – if it doesn’t work
Actually, the CBO usually does a good job optimizing “NOT IN” subqueries. Unless something goes wrong. Recently it took a long time on a 10.2.0.5 database to search for the reason. At last looking in the CBO trace files (event 10053) has given the right hint.
Continue reading