| With RELY DISABLE option not able use the FK constraints [message #419114] |
Wed, 19 August 2009 21:33  |
mr113355
Messages: 3
Registered: August 2009
|
Junior Member |
|
|
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)
|
|
|
|
|
|
| Re: With RELY DISABLE option not able use the FK constraints [message #419371 is a reply to message #419132] |
Fri, 21 August 2009 10:34  |
mr113355
Messages: 3
Registered: August 2009
|
Junior Member |
|
|
The reason we don't want to keep the constraints enabled on Instance TWO is because,
Instance TWO objects (materialized views) will regularly get refreshed from Instance ONE objects (tables). Enabled constraints will come on the way while refreshing the Materialized views from Tables from Instance ONE.
So we want to keep the constraints on Instance TWO to increase the query performance ( because oracle will ignore checking the parent keys when you have NOT NULL FK constraints) and at the same time keep the constraints with RELY disable state to allow the daily refresh from instance ONE to instance TWO.
Hope this is clear.
Thanks
|
|
|
|
|
|
| Re: With RELY DISABLE option not able use the FK constraints [message #419654 is a reply to message #419375] |
Mon, 24 August 2009 17:19 |
mr113355
Messages: 3
Registered: August 2009
|
Junior Member |
|
|
Where can you find the explicit check?
Please take a look at the Execution Plan
Under instance TWO Execution Plan, Parent Keys are refereed (here we have defined FK constraints with RELY disable option)
IXPK_TMOFDAY_DM
IXPK_GCCLLG_RF
IXPK_GCSEX_RF
Under instance ONE Execution Plan none of PK are referred, here we have all the FK constraints in enabled state.
On Instance ONE we want to keep the FK constraints in enabled state all the time, Whereas on instance TWO, We want the FK constraints, so that the parser don't check the parent table rows and have better query performance bit in DISABLED state because instance TWO get refreshed every day from instance ONE. If we have FK constraints on instance TWO they will come on the way while refreshing.
in first
Quote: 2 rows processed
in second
14 rows processed
We ran the first query on instance ONE and second on instance TWO - while testing both had different set of data, Here we're mainly looking at the execution plan result than the actual query results.
Hope this clarifies
Thanks
Bill
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
