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-lThe 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É^
