RE: disabling foreign keys

From: Timur Akhmadeev <Akhmadeev_at_NetCracker.com>
Date: Thu, 17 Feb 2011 08:28:12 +0000
Message-ID: <FFB456FD9AC03C48B943326F553A70AC11C18AF8_at_umaildb2.netcracker.com>

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 [mailto: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 <clay.colburn_at_gmail.com> 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É^ Received on Thu Feb 17 2011 - 02:28:12 CST