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

Home -> Community -> Usenet -> comp.databases.theory -> Re: all foreign key should have index?

Re: all foreign key should have index?

From: Dan <dan_at_nospam.com>
Date: Thu, 02 Feb 2006 10:14:41 -0600
Message-ID: <HdqEf.30$cJ2.7533@news.uswest.net>


On 2/1/2006 3:08 PM, Gene Wirchenko wrote:
> On Wed, 1 Feb 2006 20:17:11 +0000 (UTC), "Murdoc"
> <murdoc_0_at_hotmail.com> wrote:
>

>> Gene Wirchenko wrote:
>>
>>> On Tue, 31 Jan 2006 22:12:56 +0000, Eric Junkermann
>>> <eric_at_deptj.demon.co.uk> wrote:
>>>
>>> [snip]
>>>
>>>> When you delete a parent row, or update its key, the DBMS needs to find 
>>>> the children, either to cascade the operation or to forbid it - how can 
>>>> it do this efficiently without an index? But of course if you never do 
>>>> those things,
>>>> you might still need it to find child rows efficiently anyway.
>>>      Why does it have to be an index?

>
>> Generally: efficiency. If you want the DB to enforce referential integrity, the operation of doing
>> so needs to be efficient. A search of the entire 'FK' table to ensure that a record can be deleted
>>from the 'PK' table is (a) inefficient; and (b) pointless. Or even worse, on a cascade delete. The
>> search (when using an index) really comes down to "Is there an entry in the index for this field
>> value?".
>>
>> Again, it does come down to the size of the table. A table with a maximum of about 10 rows is not
>> going to have a large performance gain with an index (or maybe a performance detriment).

>
> That is not the point. Why does it have to be an INDEX?
>
> You have been assuming that an index is the only way. It is not.
>
> Sincerely,
>
> Gene Wirchenko
>

Please don't say "embed a pointer in the record..." Received on Thu Feb 02 2006 - 10:14:41 CST

Original text of this message

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