Star Schema Design in Oracle: Partitioning
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…
Housekeeping in Oracle: How to Get Rid of Old Data
Have you ever tried to delete a few million rows from a table with several hundred milllions of rows with a DELETE statement? If yes, then you know that this is a very bad idea. With Oracle Partitioning you can do this more elegant and…
Behind the Scenes: Preparing a Live Demo
One of the extensions in Oracle 20c is the possiblity to use the In-Memory Database option for Partitioned External Tables and Hybrid Partitioned Tables. In my opinion, this opens up many possibilities to perform efficient ad-hoc queries on Data Lakes. That’s why I prepared a…
Partition Pruning and NLS Settings
NLS (National Language Support) parameters are very useful for multilingual applications in an Oracle database. But it is important to understand their impact on query performance. I was aware that NLS is important for indexes, but never thought about its impact on partitioning. In a…
MIN/MAX Optimization and Asynchronous Global Index Maintenance
In this short post I would like to point out a non-obvious issue that one of my customers recently hit. On the one hand, it’s a typical case where the query optimizer generates a different (suboptimal) execution plan even though nothing relevant (of course, at…
Oracle 19c: Auto-List Partitioning HIGH_VALUE Bugs fixed
New features are one reason to upgrade to a new Oracle release, bug fixes of known bugs are another one. With Oracle 19c, at least two bugs related to Automatic List Partitioning were fixed. I did some tests with Auto-List Partitioning on an Oracle 19.3…
Online Statistics Gathering for ETL – Part 2
In the first part we looked at general preconditions for online statistics gathering to work and some restrictions. In this part we’ll take a look at what happens with direct path loads into partitioned tables. Partitioning There are also some points to consider while working…
Partition-Wise Operations – New Features in 12c and 18c
Partition-wise operations are not something new. I do not remember when they were introduced, but at that time the release number was still a single digit. Anyway, the aim of this post is not to describe the basics, but only to describe what is new…