Re: pros and cons

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 31 Mar 2004 11:00:27 +0100
Message-ID: <00a801c41707$03727860$7102a8c0@Primary>

There is a potential problem with foreign keys.

You need indexes to support foreign keys if

a1) you plan to update or delete the corresponding

parent key values and
a2) you want to avoid a table-locking issue on the

        child table.
    or
    b) the index that supports the foreign key happens to

be one you really wanted for efficiency reasons anyway.

So you may decide that some of your foreign key columns do not need any index support. But if you have a primary key that is supported by an index with extra columns added, then an update on the non-key columns behaves like an update on the primary key. So you could end up "KNOWING" that you aren't updating the primary key, and still seeing child table locks.

(I think I've checked this in 8.1 and 9.2, but can't remember for sure).

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

April 2004 Iceland http://www.index.is/oracleday.php June 2004 UK - Optimising Oracle Seminar

Original Message ----- From: "Sai Selvaganesan" <ssaisundar_at_sbcglobal.net> To: <oracle-l_at_freelists.org> Sent: Tuesday, March 30, 2004 11:50 PM Subject: pros and cons

: hi
:
: i read the follwing line in oracle 9i documentation,
:
: Unique and primary keys can use non-unique as well as unique indexes. They
can even use just the first few columns of non-unique indexes.
:
: i am planning to use a primary key constraint enforced by the first few
columns of a non-unique index.any feedback or advice or gotchas in having such a setup.
:
: thanks
: sai
:

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Mar 31 2004 - 03:56:54 CST