With RELY DISABLE option not able use the FK constraints We’re trying to use the RELY constraints option to make use of the foreign key constraints implicitly to reduce the query time (not checking parent keys) and at the same time not to keep the constraints enabled all the time to allow the daily data load without enable and disable the constraints (this is the data warehouse query instance ) Here is the compare between 2 instances: On instance ONE - select query runs fine without checking the parent table data explicitly (here we have FK constraints enabled all the time) But on instance TWO - we don’t want to keep the FK constraints enabled, so we used RELY option while creating the FK constraints. But, select query explicitly refers the parent table keys even if we use the RELY constraint disable. This explicit check reduces the query performance. If we enable the constraints parent key check is not performed. Attached the query output in both the cases, here is the syntax used to RELY constraint – does the syntax is wrong or is the usage of RELY is not correct? Please suggest Here is the syntax used to add the FK constraint with rely disable option: alter table esmi_mv.t2coa_txn_fct add( constraint fk1_fydate_dm_t2_txn_fct_temp foreign key(txn_dt) references FYDATE_DM(day_dt) rely disable novalidate) ------------------------------ Instance ONE (staging) ------------------------------ SQL> set autotrace on SQL> set lines 150 SQL> select distinct txn_dt 2 from( 3 select fct.txn_uid, 4 fct.txn_dt, 5 tm.tm_txt txn_tm_txt, 6 app.esrv_sfwr_cd, 7 app.esrv_sfwr_nm, 8 app.esrv_sfwr_cdesc, 9 rslt.scsfl_txn_sw, 10 rslt.txn_rsltpdesc, 11 sl.lang_txt splang_txt, 12 wl.lang_txt wplang_txt, 13 fct.ctlsb_id, 14 fct.ipaddr, 15 fct.frgn_born_ind, 16 fct.clnt_rec_found_sw, 17 fct.clnt_rec_age_sw, 18 fct.clnt_rec_sex_sw, 19 fct.age, 20 sex.sex_cdesc, 21 fct.mailg_addr_zip5, 22 fct.rsdnc_zip5, 23 fct.txn_cnt, 24 fct.dwh_app_idfr, 25 fct.prc_dt 26 from txn_fct fct 27 join app_dm app on fct.esrv_app_uid = app.esrv_app_uid 28 join txnrslt_dm rslt on fct.txn_rslt_uid = rslt.txn_rslt_uid 29 join gccllg_rf sl on fct.splang_cd = sl.lang_db_cd 30 join gccllg_rf wl on fct.wplang_cd = wl.lang_db_cd 31 join gcsex_rf sex on fct.sex_cd = sex.sex_cd 32 join tmofday_dm tm on fct.txn_tm_uid = tm.tm_uid 33 ) 34 ; Execution Plan ---------------------------------------------------------- Plan hash value: 2274958441 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 16 | 448 | 7 (58)| 00:00:01 | | | | 1 | HASH UNIQUE | | 16 | 448 | 7 (58)| 00:00:01 | | | | 2 | PARTITION RANGE ALL | | 39670 | 1084K| 3 (0)| 00:00:01 | 1 |1048575| | 3 | PARTITION LIST ALL | | 39670 | 1084K| 3 (0)| 00:00:01 | 1 | 3 | | 4 | BITMAP INDEX FAST FULL SCAN| IXB03_TXN_FCT | 39670 | 1084K| 3 (0)| 00:00:01 | 1 |1048575| ---------------------------------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 52 recursive calls 0 db block gets 75 consistent gets 0 physical reads 0 redo size 232 bytes sent via SQL*Net to client 246 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed ------------------------------ Instance TWO (query) ------------------------------ SQL> connect esmiapp1@dmdwqry1 Enter password: Connected. SQL> set autotrace on SQL> set lines 150 SQL> select distinct txn_dt 2 from( 3 select fct.txn_uid, 4 fct.txn_dt, 5 tm.tm_txt txn_tm_txt, 6 app.esrv_sfwr_cd, 7 app.esrv_sfwr_nm, 8 app.esrv_sfwr_cdesc, 9 rslt.scsfl_txn_sw, 10 rslt.txn_rsltpdesc, 11 sl.lang_txt splang_txt, 12 wl.lang_txt wplang_txt, 13 fct.ctlsb_id, 14 fct.ipaddr, 15 fct.frgn_born_ind, 16 fct.clnt_rec_found_sw, 17 fct.clnt_rec_age_sw, 18 fct.clnt_rec_sex_sw, 19 fct.age, 20 sex.sex_cdesc, 21 fct.mailg_addr_zip5, 22 fct.rsdnc_zip5, 23 fct.txn_cnt, 24 fct.dwh_app_idfr, 25 fct.prc_dt 26 from t2coa_txn_fct fct 27 join app_dm app on fct.esrv_app_uid = app.esrv_app_uid 28 join txnrslt_dm rslt on fct.txn_rslt_uid = rslt.txn_rslt_uid 29 join gccllg_rf sl on fct.splang_cd = sl.lang_db_cd 30 join gccllg_rf wl on fct.wplang_cd = wl.lang_db_cd 31 join gcsex_rf sex on fct.sex_cd = sex.sex_cd 32 join tmofday_dm tm on fct.txn_tm_uid = tm.tm_uid 33 ) 34 ; Execution Plan ---------------------------------------------------------- Plan hash value: 3267105133 -------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 672 | 82 (44)| 00:00:01 | | | | 1 | HASH UNIQUE | | 14 | 672 | 82 (44)| 00:00:01 | | | |* 2 | HASH JOIN | | 30257 | 1418K| 69 (34)| 00:00:01 | | | | 3 | INDEX FAST FULL SCAN | IXPK_TMOFDAY_DM | 2880 | 11520 | 3 (0)| 00:00:01 | | | |* 4 | HASH JOIN | | 30257 | 1300K| 63 (32)| 00:00:01 | | | | 5 | INDEX FULL SCAN | IXPK_GCCLLG_RF | 94 | 282 | 1 (0)| 00:00:01 | | | |* 6 | HASH JOIN | | 30257 | 1211K| 60 (30)| 00:00:01 | | | | 7 | INDEX FULL SCAN | IXPK_GCCLLG_RF | 94 | 282 | 1 (0)| 00:00:01 | | | |* 8 | HASH JOIN | | 30257 | 1122K| 56 (27)| 00:00:01 | | | | 9 | INDEX FULL SCAN | SYS_IOT_TOP_2300888 | 45 | 180 | 1 (0)| 00:00:01 | | | |* 10 | HASH JOIN | | 30257 | 1004K| 53 (25)| 00:00:01 | | | | 11 | INDEX FULL SCAN | SYS_IOT_TOP_2300873 | 8 | 32 | 1 (0)| 00:00:01 | | | |* 12 | HASH JOIN | | 30257 | 886K| 50 (22)| 00:00:01 | | | | 13 | INDEX FULL SCAN | IXPK_GCSEX_RF | 5 | 10 | 1 (0)| 00:00:01 | | | | 14 | PARTITION RANGE ALL | | 30257 | 827K| 46 (18)| 00:00:01 | 1 | 7 | | 15 | MAT_VIEW ACCESS FULL| T2COA_TXN_FCT | 30257 | 827K| 46 (18)| 00:00:01 | 1 | 7 | -------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("FCT"."TXN_TM_UID"="TM"."TM_UID") 4 - access("FCT"."WPLANG_CD"="WL"."LANG_DB_CD") 6 - access("FCT"."SPLANG_CD"="SL"."LANG_DB_CD") 8 - access("FCT"."TXN_RSLT_UID"="RSLT"."TXN_RSLT_UID") 10 - access("FCT"."ESRV_APP_UID"="APP"."ESRV_APP_UID") 12 - access("FCT"."SEX_CD"="SEX"."SEX_CD") Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 162 consistent gets 0 physical reads 128 redo size 388 bytes sent via SQL*Net to client 248 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14 rows processed SQL>