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.
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.
An almost unknown simplification in the usage of dbms_xplan is possible since Oracle 12.2. Sometimes, there are small details that makes our job (a little bit) easier.
Since Oracle 12c, the query optimizer is able to change execution plans at runtime. This feature is called “Adaptive Plans”. Something similar happened on the ODTUG Community Service Day at the Kscope conference in Orlando.
I just wanted to write a small demo script for Auto-List Partitioning in Oracle 12.2. Instead, I spent an entire evening finding a bug in the Oracle data dictionary. Fortunately, it’s not a dramatic one.
Direct-Path INSERT is a very efficient way to load data into data warehouse tables. The load performance can even be improved with NOLOGGING on the target table. In Oracle 12c Release 2, this is even possible for Oracle Data Guard environments. But is it a good idea?
Incremental statistics reduce the time to gather global statistics for a partitioned table. Really? In some situations it may happen that incremental statistics slow down statistics calculation dramatically. An example of a real project in Oracle 12.1 and how it can be improved with Oracle 12.2.
This year, I had the opportunity to present three sessions at the DOAG conference in Nuremberg – one on each conference day. Here a short summary of the sessions and links to the downloads.
Hash keys are often used as primary keys in Data Vault environments. When loading dimensional data marts from a Data Vault schema, it seems to be obvious to use them as dimension keys, too. At least in combination with Oracle Database In-Memory, this is not a good idea.
With Oracle Database In-Memory, it is possible to populate individual columns of a table into the In-Memory Column Store. This is very useful for large tables, if only the frequently used columns should be populated to safe memory. The SQL syntax to define this seems to be straight-forward, but does not always work as expected.
Data Warehousing with Oracle 