Home » SQL & PL/SQL » SQL & PL/SQL » With RELY DISABLE option not able use the FK constraints (Oracle 11g)
With RELY DISABLE option not able use the FK constraints [message #419114] Wed, 19 August 2009 21:33 Go to next message
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 #419132 is a reply to message #419114] Thu, 20 August 2009 00:28 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
But on instance TWO - we don't want to keep the FK constraints enabled,

Why please

http://tkyte.blogspot.com/2006/01/stuck-in-rut.html

[Updated on: Thu, 20 August 2009 01:56]

Report message to a moderator

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 Go to previous messageGo to next message
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 #419375 is a reply to message #419371] Fri, 21 August 2009 11:25 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
But, select query explicitly refers the parent table keys even if we use the RELY constraint disable. This explicit check reduces the query performance


Where can you find the explicit check?

Further there is difference of data between both instances.

in first
2  rows processed

in second
14  rows processed

[Updated on: Fri, 21 August 2009 11:26]

Report message to a moderator

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 Go to previous messageGo to next message
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
Re: With RELY DISABLE option not able use the FK constraints [message #419656 is a reply to message #419114] Mon, 24 August 2009 18:46 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>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?
I can not find any Oracle manual on RELY where it states or implies that RELY can speed up SELECT.
All documents state that RELY may speed up certain DML operations.
Re: With RELY DISABLE option not able use the FK constraints [message #419666 is a reply to message #419656] Mon, 24 August 2009 23:20 Go to previous message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
I can not find any Oracle manual on RELY where it states or implies that RELY can speed up SELECT.

me too

http://tkyte.blogspot.com/2006/01/stuck-in-rut.html

Referential keys add information about the data into the data dictionary. Using this additional information Oracle is able to perform a query rewrite more often, in many complex cases.

So after performing you inserts and updates with foreign keys suspended, REBUILD them and run the SELECT again and please tell the difference




[Updated on: Tue, 25 August 2009 00:52]

Report message to a moderator

Previous Topic: ora 4127 single subquery returns more than one row"
Next Topic: Please Help in Pivot Query
Goto Forum:
  


Current Time: Sat Dec 03 10:03:44 CST 2016

Total time taken to generate the page: 0.12668 seconds