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.
Oracle 23c has a lot of small, but useful enhancements that makes life of developers easier. For example two new columns in the data dictionary views for partition metadata.
Since a few days, Oracle Database 23c Free Developer Release is available. It contains many new features that are interesting for developers, especially some nice little extensions in SQL that we missed since years. Let’s start with an easy one in this blog post.
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.
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.
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.
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.
I work with SQL since decades, and I think to have some practical experience with writing SQL queries on Oracle databases. So why should I buy a book about practical Oracle SQL? If you are in a similar situation like me, read this book review.
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.
If you work with Data Vault for a data warehouse running in an Oracle database, I strongly recommend to use Oracle 12.2 or higher. Why that? Since Oracle 12c Release 2, join elimination works for more than one join column. This is essential for queries on a Data Vault schema.
Data Warehousing with Oracle 