Home » RDBMS Server » Performance Tuning » foreign keys affect performance?
foreign keys affect performance? [message #65089] Tue, 27 April 2004 00:07 Go to next message
Grigoriadis
Messages: 7
Registered: March 2004
Junior Member
Hi,

do foreign keys cause a ordinary table scan on the child table in the case of an update on the parent table?

Oracle documentation says, that index on the child table column is important due to share locking issues. They give an answer to the question WHEN the foreign key constraint check but not on HOW it takes place. That would be my question.

Thanks a lot in advance,

Grigoriadis
Re: foreign keys affect performance? [message #65094 is a reply to message #65089] Wed, 28 April 2004 12:01 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
see this link...
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:292016138754
Re: foreign keys affect performance? [message #65097 is a reply to message #65094] Wed, 28 April 2004 23:14 Go to previous messageGo to next message
Gigoriadis
Messages: 1
Registered: April 2004
Junior Member
Thanks for responding,

in my case it's not about "delete cascade" or quering, it's about updating or deleting rows of the parent table, which is the case where Oracle has to look up for possibly existing child records.

Sorry but the link you included, only shows a part of the oracle documentation, I already have read, about indexes on foreign keys because of the share locks on child records.

Not a mention of the performance issue.

How does oracle find out if there are existing child records, in case of a delete operation on the parent table? Would oracle perform a simple query like the following:

select count(*)
from child_table
where pk_field_of_parenttable=...;

In this case, yes, an index on the foreign is needing also for performance reasons or the other way round, deleting the foreign key would speed up such parent-deletes.

Am I right, or does Oracle handle those child-record look-ups diffenently?
Re: foreign keys affect performance? [message #65103 is a reply to message #65097] Thu, 29 April 2004 10:45 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Oracle uses recursive SQL to enforce FK constraints:
CREATE TABLE PARENT
(
  COL1  NUMBER                                  NOT NULL,
  COL2  NUMBER                                  NOT NULL);
--
CREATE TABLE CHILD
(
  COLA  NUMBER                                  NOT NULL,
  COL1  NUMBER,
  COL2  NUMBER);
--
ALTER TABLE PARENT ADD (
  CONSTRAINT PARENT_PK PRIMARY KEY (COL1, COL2));
--
ALTER TABLE CHILD ADD (
  CONSTRAINT CHILD_PK PRIMARY KEY (COLA));
--
ALTER TABLE CHILD ADD (
  CONSTRAINT CHILD_FK_TO_PARENT FOREIGN KEY (COL1, COL2) 
    REFERENCES PARENT (COL1,COL2));
--
insert into parent values (1, 1);
insert into child values (100, 1, 1);
--
alter session set sql_trace true;
delete parent;
alter session set sql_trace false;
-----------------------------------
--Extract from the trace file:
-----------------------------------
select count(1)
from
 "SCOTT"."CHILD" where "COL1" = :1 and "COL2" = :2

--
-- then the pl/sql takes over to evaluate the result of the select...
--
Previous Topic: TKPROF
Next Topic: TKPROF HELP
Goto Forum:
  


Current Time: Thu Mar 28 13:30:24 CDT 2024