In the last two weeks I was very busy: three conferences in ten days are exhausting – but it was a lot of fun. The last conference for this year is over now. Time for my personal conference review that I write since a couple of years. When I started to work for Callista a year ago, my new employer offered to pay my travelling expenses for up to six international conferences a year. Thanks a lot to Callista to support me. And what a surprise: I was able to speak at exactly six international conferences in 2023. Precision landing! It is not possible to write down all the impressions from all th ..read more

In the third part of this blog post series about star schema design, let’s have a look at a powerful, but for many people still unknown feature: Oracle Database In-Memory enables massive performance improvements for typical data warehouse queries. Oracle Database In-Memory (aka “In-Memory option”) was introduced with Oracle 12c. Although this is ten years ago, the feature is still not very widely used in many Oracle data warehouses. This is sad, because it is a powerful performance features, especially for star schemas. It would go too far to describe all the technical details and configuratio ..read more

The newest version of the Oracle sample schema “Sales History” (SH) finally contains current data again and is easier to install than the previous versions. If you work with star schemas, the SH schema is a must. Last week, I had the pleasure to teach a group of students about the basics of dimensional modeling and how to write SQL queries on a typical star schema. For the exercises we used livesql.oracle.com and the sample schema “Sales History” (SH) on Oracle 19c. Although it is quite old, this schema is still useful to test and show many features and examples based on dimension and fact tab ..read more

Oracle 23c is like Christmas for me. There are many surprises to be found. This weekend I discovered another nice little enhancement. In the last days, I developed and tested some demo scripts that I plan to show at my next conference sessions at APEX connect and ODTUG Kscope. It is a matter of honor that I will show these live demos on Oracle Database 23c Free, although the main focus of my presentations are not the new features of this release. In one demo, I will explain why function calls in WHERE conditions should be avoided. Last year, I also wrote a blog post about this topic. When ..read more

Partitioning is one of the most powerful features for data warehouses in Oracle databases. In this blog post, I explain how it can be used for the physical design of star schemas. What is the recommended partitioning strategy for a star schema, and what are the advantages of partitioning? In my blog post series about star schema design in Oracle databases, I started with the fundamental rules about constraints and indexes. For details, read the previous blog post. In this post, I will write about an important feature in many data warehouses: Partitioning. Preliminary Remarks Partitioning is n ..read more

It has become almost a tradition for me to write an annual review of my conferences and presentations. The good news: In 2022, most of the conferences were on-site again. Additionally, there was another important event for me in 2022: I changed my job and work now for Callista. When I stood on our balcony on New Year’s Eve a year ago together with my wife and some of our friends and toasted the New Year, I already suspected that there could be some changes for my job situation in 2022. My employer Trivadis has been aquired by a large consulting company, and this will certainly have some i ..read more

What are the design rules for good performance in a star schema in an Oracle database? This blog post series introduces some recommendations for the physical database design. This first post is about constraints and indexes. For the last few years, I was supporting one of my customers in solving performance problems on their Oracle databases. I have been involved with all kind of applications and sometimes really complex SQL statements. But the very last performance ticket I had to solve was a classical one: Several queries on a star schema (with a very large fact table) used a lot of database ..read more

There are many books on performance optimization, but this one differs in a few ways. It is more of an entertaining collection of short stories, but with many educational messages. My travel plans for the Kscope22 conference in June this year were suboptimal: My return flight was already in the morning of the last conference day – exactly the time when Cary Millsap presented his deep dive session “Faster: How to Optimize a System”. Unfortunately, I was not able to join his presenatation about his new book. Very sad! I met Cary during the conference in a lunch break and told him about my poor t ..read more

In the Oracle community, the terms “Data Vault” and “Database Vault” are often confused, although they have nothing to do with each other. A short clarification. Some time ago I had a meeting with the development team and the database administrator of one of my customers. The topic was what database schemas we needed for a new data warehouse. At some point the keyword “Data Vault” came up, which caused the DBA to react surprised. She asked if we had discussed this with the security officer. Now I was surprised. Why should the security manager care about our data modelling method? After a short ..read more

A typical reason of many performance issues with SQL are function calls in the WHERE condition. They make it difficult for the query optimizer to determine the cardinality. Fortunately, there are several ways to solve this problem. Function calls – or any kind of SQL expressions in a WHERE condition – can cause performance issues in complex queries. The reasons are not the functions themselves, but the calculation of the cardinality by the optimizer. They cause wrong estimations and can lead to inappropriate join orders and suboptimal execution plans. I will show this behaviour with a sim ..read more