Foreign Key in Oracle Data Warehouse - Best Practice

My last article "Why should we create Primary and Foreign Keys (Constraints) in Database when data consistency is guaranteed by Application?" was well taken by many developers/DBAs, but some data-warehouse developers/DBAs did not receive it well. They argued that in Data-Warehouse some times it is impossible (very difficult) to create Foreign Keys on the Fact tables.

There were mainly following 2 reasons against Foreign Keys:
- Validation of Foreign Keys in Fact table is an overhead because ETL process has already verified the referential integrity of fact tables
- Foreign Keys in fact tables, that dimension tables need to be loaded first and then we can load fact tables. It restrict ETL process to load both Dimension and Fact tables in parallel.

Above both points are valid in-terms of ETL loads, but without Foreign key Oracle Optimizer will miss vital information. Foreign Keys enforce data consistency and validate fact tables has no other data than of dimension table. Foreign Keys helps Oracle Optimizer to generate best execution plan. It enables CBO to perform query transformation like "Join Elimination" and "Query Rewrite" on Materialized Views. Last but not the least, Foreign Keys are self documentation tool and having Foreign Keys enables developers to easily find tables relation.

So now the Million Dollar Question is, Does Oracle provides any solution where we can get all benefits on Foreign Keys and can avoid the restrictions like slowing down the ETL load?

Again the answer was already there in Oracle Documentation (Data Warehousing Guide):
https://docs.oracle.com/database/121/DWHSG/schemas.htm#DWHSG9053

There can be following states of any constraints
- DEFFERED / NOT DEFFERED - constraint checking can be deferred until the end of the transaction (COMMIT)
- ENABLE / DISABLE - An enabled constraint ensures that all new data modifications upon table satisfy the conditions of the constraints.
- VALIDATE / NOVALIDATE - Validated constraint ensure that all data that currently resides in the table satisfies the constraint.
- RELY / NORELY - The RELY state provides you with a mechanism to tell Oracle that a given constraint is believed to be true, even if it is in DISABLE and NOVALIDATE state.

RELY Constraints in a Data Warehouse are not used for data validation, but they:
- Enable more sophisticated Query Rewrites for materialized views.
- Enable Optimizer Query transformation (Join Elimination) by CBO.
- Enable tools to retrieve information regarding table relations directly from the Oracle.

Creating a RELY constraint is inexpensive and does not impose any overhead during ETL load. If Foreign Key is in RELY state, the Primary Key referred must also be in RELY state.

Now let's come to the practical part. Lets assume we have following basic Star Schema, each dimension table has Primary Key defined. Lets modify our tables as per suggestion of Oracle Data Warehousing Guide.

Step 1: Modify Primary Keys of all Dimension tables to enable RELY

nimish@garg> alter table d_date modify primary key rely;
Table altered

nimish@garg> alter table d_product modify primary key rely;
Table altered

nimish@garg> alter table d_supplier modify primary key rely;
Table altered

Step 2: Create Foreign Keys on Fact table with RELY disable novalidate

nimish@garg> alter table f_sales add FOREIGN KEY(d_date_id)
  2  REFERENCES d_date(d_date_id)
  3  RELY disable novalidate;
Table altered

nimish@garg> alter table f_sales add FOREIGN KEY(d_supplier_id)
  2  REFERENCES d_supplier(d_supplier_id)
  3  RELY disable novalidate;
Table altered

nimish@garg> alter table f_sales add FOREIGN KEY(d_product_id)
  2  REFERENCES d_product(d_product_id)
  3  RELY disable novalidate;
Table altered

The last thing to note here is to enable Query Rewrite and Join Elimination we need to set query_rewrite_enabled = TRUE and query_rewrite_integrity = TRUSTED for session or at system level. To conclude this article I must say RELY constraints are very powerful and unique feature of Oracle Database and is there from Oracle Database 8i (or before) but still very unknown. RELY constraints enables us to achieve all benefits on Foreign Keys and can avoid the restrictions which slows down the ETL processing.


Related Posts:
- Complex View Merging Transformation and ORA-00979 in Oracle 12c
- VIEW PUSHED PREDICATE - A Powerful Optimizer Transformation feature
- SQL Tuning: Partition Pruning and HASH JOIN - Real Life Example
- How to Get Execution Plan and Statistics of SQL Query
- Top 15 new features of Oracle Database 12.2 for developers