Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: the scoop on indexing foreign keys

Re: the scoop on indexing foreign keys

From: Connor McDonald <hamcdc_at_yahoo.co.uk>
Date: Sat, 14 Sep 2002 11:43:19 -0800
Message-ID: <F001.004D036D.20020914114319@fatcity.com>


The 8.1.7 doco says:

"Figure 25-8 illustrates the locking mechanisms used by Oracle when no index is defined on the foreign key and when rows are being updated or deleted in the parent table. Inserts into the parent table do not require any locks on the child table. "

thus updates and deletes are not distinguished.

And this behaviour can be proved:

Session 1:



SQL> create table par ( x number primary key );

Table created.

SQL> insert into par values (1);

1 row created.

SQL> insert into par values (2);

1 row created.

SQL> create table chi ( c number, x number);

Table created.

SQL> alter table chi add constraint chi_fk   2 foreign key ( x) references par(x);

Table altered.

SQL> insert into chi values (999,2);

1 row created.

SQL> insert into chi values (998,2);

1 row created.

SQL> commit;

Commit complete.

Then either of the two following changes: SQL> delete from par where x = 1;
SQL> update par set x =1 where x = 1;

locks the following statement in Session 2:

insert into chi values (997,2);

hth
connor


Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net

"Remember amateurs built the ark - Professionals built the Titanic"



Do You Yahoo!?
Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: hamcdc_at_yahoo.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Sat Sep 14 2002 - 14:43:19 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US