Hi,
I am designing a DWH physical layer and confused if it's better to create FK constraint in disable mode. Our nightly bulk loads are going to be very heavy and worried on the performance of nightly loads if we keep FK constraint enabled.
My question is that, if we maintain the constraint in disable mode, will Oracle explain plan identify that there is a constraint on the column and take the benefit of same in execution plan?
I was going through the following link
http://www.oracle.com/technetwork/testcontent/o39asktom-096149.html The link states constraint will help Oracle in generating good explain plans. So in same context, if we maintain FK or some other constraint in disable mode, will it help explain plan?
First up, are you sure having an enabled FK slows performance enough for you to consider disabling it?
i.e. have you actually tested with an without the constraint and have found a big enough benefit to consider disabling it?
If not, do this first!
The optimizer can use a disabled FK when creating plans,
provided it's set to rely.
But you also need to set query_rewrite_integrity = trusted to take advantage of this:
SQL> create table t (
2 x int primary key rely
3 );
Table created.
SQL>
SQL> create table t_chd (
2 x int references t(x) rely disable ,
3 y int
4 );
Table created.
SQL>
SQL> insert into t values (1);
1 row created.
SQL> insert into t_chd values (1, 1);
1 row created.
SQL> insert into t_chd values (1, 2);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> set autotrace trace exp
SQL> select t_chd.* from t join t_chd on t.x = t_chd.x;
Execution Plan
----------------------------------------------------------
Plan hash value: 3718207833
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 78 | 3 (0)| 00
| 1 | NESTED LOOPS | | 2 | 78 | 3 (0)| 00
| 2 | TABLE ACCESS FULL| T_CHD | 2 | 52 | 3 (0)| 00
|* 3 | INDEX UNIQUE SCAN| SYS_C0025359 | 1 | 13 | 0 (0)| 00
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T"."X"="T_CHD"."X")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
SQL> alter session set query_rewrite_integrity = trusted;
Session altered.
SQL> select t_chd.* from t join t_chd on t.x = t_chd.x;
Execution Plan
----------------------------------------------------------
Plan hash value: 3795753992
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 52 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_CHD | 2 | 52 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T_CHD"."X" IS NOT NULL)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Notice in the second plan, the index unique scan is gone!
For further discussion about the pros and cons of FKs in a DWH, check out Dani Schnider's blog:
https://danischnider.wordpress.com/2015/12/01/foreign-key-constraints-in-an-oracle-data-warehouse/