FAQ
I have a database that is a data warehouse, so we bulk load data daily and
tune the database for heavy read operations. I noticed that all of the
foreign keys in the database are disabled. This obviously aids in the speed
of the loading process and the relationships can be verified during the ETL
process.

My question is, are there any benefits to having these disabled constraints
laying around beyond just having a sort of meta-data description of the
foreign key relationships? Does the optimizer use these at all?

Thanks!

Search Discussions

  • Greg Rahn at Feb 16, 2011 at 12:20 am
    If they are disabled, no, the optimizer can not use them. Other tools
    may use the metadata and even if they are in a disabled state (ERD
    tools etc) which may be desirable.
    On Tue, Feb 15, 2011 at 3:16 PM, Clay Colburn wrote:
    I have a database that is a data warehouse, so we bulk load data daily and
    tune the database for heavy read operations.  I noticed that all of the
    foreign keys in the database are disabled.  This obviously aids in the speed
    of the loading process and the relationships can be verified during the ETL
    process.
    My question is, are there any benefits to having these disabled constraints
    laying around beyond just having a sort of meta-data description of the
    foreign key relationships?  Does the optimizer use these at all?
    Thanks!
    --
    Regards,
    Greg Rahn
    http://structureddata.org
    --
    http://www.freelists.org/webpage/oracle-l
  • Timur Akhmadeev at Feb 17, 2011 at 8:28 am
    But you can make CBO to rely on the constraint even if it is disabled:

    drop table t1 cascade constraints purge;
    drop table t2 cascade constraints purge;

    create table t1 as select rownum id, cast('x' as char(100)) pad from all_objects;
    create table t2 as select * from t1;

    alter table t1 add constraint t1_pk primary key(id);
    alter table t2 add constraint t2_fk foreign key (id) references t1 disable;

    explain plan for
    select count(*) from t1, t2 where t1.id = t2.id;
    _at_xp

    alter table t2 modify constraint t2_fk rely;
    explain plan for
    select count(*) from t1, t2 where t1.id = t2.id;
    _at_xp

    Plan hash value: 700556318
    Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    0 | SELECT STATEMENT | | 1 | 26 | 178 (11)| 00:00:03 |
    1 | SORT AGGREGATE | | 1 | 26 | | |
    2 | NESTED LOOPS | | 37298 | 947K| 178 (11)| 00:00:03 |
    3 | TABLE ACCESS FULL| T2 | 37298 | 473K| 162 (2)| 00:00:02 |
    * 4 | INDEX UNIQUE SCAN| T1_PK | 1 | 13 | 0 (0)| 00:00:01 |
    Predicate Information (identified by operation id):

    4 - access("T1"."ID"="T2"."ID")

    Plan hash value: 3321871023
    Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    0 | SELECT STATEMENT | | 1 | 13 | 162 (2)| 00:00:02 |
    1 | SORT AGGREGATE | | 1 | 13 | | |
    * 2 | TABLE ACCESS FULL| T2 | 37298 | 473K| 162 (2)| 00:00:02 |
    Predicate Information (identified by operation id):

    2 - filter("T2"."ID" IS NOT NULL)

    Regards
    Timur Akhmadeev
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Greg Rahn
    Sent: Wednesday, February 16, 2011 3:20
    To: clay.colburn_at_gmail.com
    Cc: oracle-l
    Subject: Re: disabling foreign keys

    If they are disabled, no, the optimizer can not use them. Other tools
    may use the metadata and even if they are in a disabled state (ERD
    tools etc) which may be desirable.
    On Tue, Feb 15, 2011 at 3:16 PM, Clay Colburn wrote:

    I have a database that is a data warehouse, so we bulk load data daily and
    tune the database for heavy read operations.  I noticed that all of the
    foreign keys in the database are disabled.  This obviously aids in the speed
    of the loading process and the relationships can be verified during the ETL
    process.
    My question is, are there any benefits to having these disabled constraints
    laying around beyond just having a sort of meta-data description of the
    foreign key relationships?  Does the optimizer use these at all?
    Thanks!
    --
    Regards,
    Greg Rahn
    http://structureddata.org
    --
    http://www.freelists.org/webpage/oracle-l

    The information transmitted herein is intended only for the person or entity to which it is addressed and may contain confidential, proprietary and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.

    †Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^
  • Greg Rahn at Feb 17, 2011 at 5:28 pm
    Generally I think most who desire this behavior use "rely disable
    novalidate" for their FKs.
    http://download.oracle.com/docs/cd/E11882_01/server.112/e16579/constra.htm#i1006284

    On Thu, Feb 17, 2011 at 12:28 AM, Timur Akhmadeev
    wrote:
    But you can make CBO to rely on the constraint even if it is disabled:
  • Clay Colburn at Feb 18, 2011 at 1:13 am
    This is great info, thanks so much guys!
    On Thu, Feb 17, 2011 at 9:28 AM, Greg Rahn wrote:

    Generally I think most who desire this behavior use "rely disable
    novalidate" for their FKs.

    http://download.oracle.com/docs/cd/E11882_01/server.112/e16579/constra.htm#i1006284

    On Thu, Feb 17, 2011 at 12:28 AM, Timur Akhmadeev
    wrote:
    But you can make CBO to rely on the constraint even if it is disabled:
    --
    Regards,
    Greg Rahn
    http://structureddata.org
    --
    http://www.freelists.org/webpage/oracle-l
  • Clay Colburn at Mar 25, 2011 at 5:17 pm
    When trying to use "rely disable novalidate" I get the following error:

    SQL Error: ORA-25158: Cannot specify RELY for foreign key if the associated
    primary key is NORELY

    I looked at the table that contains the primary key and found that the RELY
    flag was not set (null). After setting the rely flag on the primary key, I
    successfully created the foreign key constraint with rely enabled.

    The only documentation that I've been able to find talks about this flag
    allowing oracle to increase query rewrite performance by assuming that data
    is referentially correct (http://tinyurl.com/6z3v9h8). By this logic, why
    does it matter if you set it on the primary key? What are you trying to
    assert about valid data by setting on the primary key? Wouldn't the
    assertion about validity only come in when linking a foreign key to the
    primary key?

    Hopefully I'm just missing something obvious here :)

    Thanks everyone!
    On Thu, Feb 17, 2011 at 9:28 AM, Greg Rahn wrote:

    Generally I think most who desire this behavior use "rely disable
    novalidate" for their FKs.

    http://download.oracle.com/docs/cd/E11882_01/server.112/e16579/constra.htm#i1006284

    On Thu, Feb 17, 2011 at 12:28 AM, Timur Akhmadeev
    wrote:
    But you can make CBO to rely on the constraint even if it is disabled:
    --
    Regards,
    Greg Rahn
    http://structureddata.org
    --
    http://www.freelists.org/webpage/oracle-l
  • Hemant K Chitale at Feb 16, 2011 at 8:13 am
    Are they deliberately left disabled after the loading OR is it inadvertent?

    Hemant K Chitale

    sent from my smartphone
    On 16 Feb 2011 07:17, "Clay Colburn" wrote:
    I have a database that is a data warehouse, so we bulk load data daily and
    tune the database for heavy read operations. I noticed that all of the
    foreign keys in the database are disabled. This obviously aids in the speed
    of the loading process and the relationships can be verified during the ETL
    process.

    My question is, are there any benefits to having these disabled
    constraints
    laying around beyond just having a sort of meta-data description of the
    foreign key relationships? Does the optimizer use these at all?

    Thanks!
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 15, '11 at 11:16p
activeMar 25, '11 at 5:17p
posts7
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase