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.
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 database.
The data type of primary key and foreign key columns has an impact on the query performance. This should be considered in the design decision of keys in a Data Vault schema.
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.
In the last couple of days, I joined several interesting events in Colorado. First, I was able to take part of the “Advanced Data Vault & Ensemble Modeling Summit” at Genesee Academy. After that, I attended the first “Global Data Summit” conference in Golden, Colorado. And finally, I was invited to a very special dinner of the “Boulder Business Intelligence Brain Trust”.
Hash keys can be used instead of sequence numbers to build surrogate keys in a Data Warehouse. This is often done in Data Vault environments, and there are some good reasons to do that. But there are also several issues and restrictions with hash keys. So, use hash keys only if you have good reasons for them. And if you decide to use hash keys in your Oracle Data Warehouse, make sure that you do it the right way.
