There are many new extensions for Partitioning in Oracle 12c Release 2 (12.2). One of them is the CREATE TABLE FOR EXCHANGE statement. Some years ago, I wished to have this feature for a particular problem with hidden columns.
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.
Delta detection is a common task in every Data Warehouse. It compares new data from a source system with the last versions in the Data Warehouse to find out whether a new version has to be created. There are several ways to implement this in Oracle.
Dynamic SQL is a wonderful feature with a lot of flexibility. Unfortunately, it is not trivial to see the executed SQL statements. A simple trick helps to test and debug PL/SQL code with Dynamic SQL.
Since Oracle 12c, table statistics are gathered automatically for some bulk operations. This inconspicuous new feature called “Online Statistics Gathering for Bulk Loads” is very practical for ETL jobs in Data Warehouses.
Foreign key constraints are often omitted in Data Warehouses. This blog post explains why this is not a good approach and what is a practical compromise for foreign key constraints in an Oracle Data Warehouse.
Loading data into a Data Vault Model is easy. But how complex is it to extract the data from a Data Vault into a dimensional Data Mart? A Point in Time (PIT) table helps to load Dimensions from a Hub with multiple Satellites.