| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Ref.Constraint: Index on Foreign Key?
Thomas Kyte wrote:
>
> A copy of this was sent to Achim Reiners <areiners_at_template.de>
> (if that email address didn't require changing)
> On Fri, 29 Jan 1999 08:50:05 +0100, you wrote:
>
> >Hello,
> >
> >I'm not familiar with Sybase/Oracle and have a question:
> >
> >If I construct a Referential Constraint,
> >does Sybase/Oracle automatically implicit construct
> >an Index on the Foreign Key column if not already exist?
> >
> >Someone told me that this is not the case for Oracle and Sybase.
> >I know that in INFORMIX does automatically construct an index
> >on every foreign key column.
> >
> >If I was told correctly I wonder how the Ref.Constraint
> >is efficiently checked at e.g. inserts/updates on the foreign key
> >columns in sysbase and oracle.
> >
> >Thanks for any comments
> >
> >
> >Achim
>
> It is true that in Oracle no index is created (however if one exists, it will be
> used to help in the performance of constraint enforcement in 2 cases).
>
> Its not a performance hit unless you muck around with the primary key of the
> parent table or delete from the parent table tho.
>
> For an insert into a child, its a keyed read into the parent.
> For an update of the fkey columns in a child, its a keyed read into the parent.
> For a delete of a child record, there are no operations on the parent.
>
> For an insert of a parent, there are no operations on a child.
>
> For an update of a parent primary key, a scan of the child would have to take
> place to ensure no child keys point to this parent. (expensive but you shouldn't
> do this anyway).
>
> For a delete of a parent record, a scan of the child would take place again to
> either error out the delete if a child is found or to cascade the delete.
>
> So, if you never update or delete a parent record (many apps have this
> characteristic) the index *may* be not needed (but it probably is, typically in
> a parent/child relationship you query the child table via its foreign key to the
> parent to get the parent/child relationship on the screen). It is recommended
> therefore but not mandatory that you create indexes on foreign keys.
>
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Service Industries
> Reston, VA USA
>
> --
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
>
> Anti-Anti Spam Msg: if you want an answer emailed to you,
> you have to make it easy to get email to you. Any bounced
> email will be treated the same way i treat SPAM-- I delete it.
Hello Thomas,
Isn't locking also a consideration with indexes on foreign keys? Consider the following example (7.3.3.5)
create table t_parent
( x number primary key ) ;
begin
for i in 1 .. 100 loop
insert into t_parent values (i);
end loop;
commit;
end;
/
create table t_child
( a number primary key,
x number );
alter table t_child add constraint t_child_fk foreign key ( x ) references t_parent (x);
begin
for i in 1 .. 100 loop
insert into t_child values (i,i);
end loop;
commit;
end;
/
In Session 1:
delete from t_child where x = 10;
delete from t_parent where x = 10;
(no commit or rollback)
In Session 2:
insert into t_child values (200,3); -- unrelated values
then I don't get the lock...
Cheers
--
 |
 |