In summer time, the nights are very short. For some Data Warehouses, this is the case all year round, but not because of late sunset and early sunrise. The night is not long enough to finish all the ETL jobs. Long-running load jobs that run for several hours are not a seldom situation. Here some tips how this can be avoided.
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.