Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: check constraints - how implemented and how fast????
A copy of this was sent to Steve Perry <sperry_at_sprynet.com>
(if that email address didn't require changing)
On Sun, 13 Dec 1998 11:21:56 -0800, you wrote:
>Can anyone tell me how check constraintes are implemented on Oracle
>7.3.x or 8.x?
>
>I've been in the habit of creating indexes on all foreign keys. I
>assumed this would speed up the lookup when trying to delete a parent
>row that has child rows from another table using the key. I used to use
>triggers to implement the restriction, which needed the index for speed.
>I was talking to a friend and they said it's not needed with check
>constraints, but they couldn't tell me how Oracle does it.
>
the server concepts manual has a good discussion on check constraints in general.
indexes on foreign keys are not needed, this is true, however they are suggested for the exact reasons above -- speed.
If you delete the parent, it will do a search on the child records. if the foreign key fields in the child are not indexed -- full scan. Also, you would want to check out chapter 6 (chapter 9 in the O8 doc set) in the application developers guide, the section on "Concurrency Control, Indexes, and Foreign Keys"
>Thanks,
>Steve
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 Sun Dec 13 1998 - 13:29:48 CST