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.
There are many good reasons to use ANSI join syntax instead of the old Oracle join syntax. With one exception: If you want to use your Materialized Views for Query Rewrite, avoid to write them with ANSI joins.
After creating local and global partial indexes, the last part of this blog post trilogy shows how partial indexes are used by the optimizer for SQL queries – especially for queries in Data Warehouse environments.
Partial Indexes, introduced with Oracle 12c, are available for local and global indexes. In part 2 of this blog post trilogy, let’s focus on global partial indexes on a partitioned table.
Sometimes it would be useful to create indexes for only some of the partitions of a partitioned table. Oracle 12c supports this possibility with Partial Indexes. This blog post shows how local partial indexes can be created – even on an Oracle 11g database.
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.