Category Archives: Data Warehouse

Oracle 26ai: A Closer Look at JOIN TO ONE

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

CALENDAR Functions in Oracle 26ai – Part 2

Do I Still Need a Time Dimension?

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

CALENDAR Functions in Oracle 26ai – Part 1

First Impressions and Practical Observations

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

Fix Optimizer Estimate Issues from Implicit Conversions #JoelKallmanDay

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

When Compression Expands: The Hidden Pitfalls of HCC

Hybrid Columnar Compression (HCC) in Oracle Exadata is a prime example of how physical infrastructure can significantly enhance the efficiency of data management and analysis—when used correctly. This is an area that also affects developers and data engineers: what it is, how it works, and what to expect. If HCC is used in your project, then everyone on the team should be aware of it, including the specifics of what, where, and how it is implemented. Otherwise, there may be a few surprises, as in the example I encountered recently: instead of achieving the expected storage savings and improved performance, there was a huge increase in space consumption, along with negative performance effects. Read on to find out what happened.

Continue reading

Online Statistics Gathering: Update 2024

Online Statistics Gathering has been introduced in Oracle 12c. I think it is a very important building block to establish a consistent approach in dealing with optimizer statistics in your ETL jobs. I have already written some posts about it a while ago (first, second and third). When I was preparing a talk about optimizer statistics for data engineers recently, I took a closer look at it again. Oracle has now continuously improved the feature over several versions and has lifted many restrictions. Let’s see what I mean.

Continue reading

Object Statistics in Your Data Pipelines

As my electrical engineering lecturer at university used to joke, there are only two faults in electricity: 1) No contact where it is needed. 2) There is contact where it is not needed. You can often think the same way when looking for the causes of a poor execution plan: no stats were gathered when the optimizer needed them for a good execution plan or someone gathered stats when it was inappropriate.

Continue reading

Issue with the Hint ENABLE_PARALLEL_DML

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 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