| 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?
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
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. Received on Fri Jan 29 1999 - 07:13:26 CST
 |
 |