SQL Performance Tips in Execution Plans of Oracle 23c

Oracle 23c is like Christmas for me. There are many surprises to be found. This weekend I discovered another nice little enhancement.

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 the advantages of partitioning?

Star Schema Design in Oracle: Fundamentals

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.

Book Review: “Faster: How to Optimize a System” by Cary Millsap

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.

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 much faster.

Performance Tips: PL/SQL Functions in SQL Queries

PL/SQL functions in SQL statements may cause major performance problems. If it is not possible to avoid these calls, there are several solutions to improve the performance. But not for all situations.

Performance Tips: Function Calls in WHERE Conditions

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.

Performance Comparison: Snowflake vs Oracle Autonomous Data Warehouse

Which data warehouse cloud platform is faster: Oracle Autonomous Data Warehouse or Snowflake Cloud Data Warehouse? The short answer: it depends. For a more detailed answer, read this blog post.

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 demo script for my DOAG presentation about SQL features in Oracle 20c. Unfortunately, it turned out differently than planned. A drama in four acts.

Design Tips for Query Rewrite

Although Query Rewrite on Materialized Views is a powerful feature which is available in Oracle databases for a long time, it is rarely used. Many developers and database administrators don’t know about it or do not use it because they think it is too complex. In this blog post, I try to reduce the concerns about complexity with some general design tips for Query Rewrite.