Re: REF column and index

"Stéphane Rondinaud" <sNOrondin_at_SPAMclub-internetPLEASE.fr> wrote in message news:pan.2004.06.27.21.08.50.886767_at_SPAMclub-internetPLEASE.fr...
> Hi Fred,
>
> I hope I won't mess around too much for a first post on this NG...
>
> On Thu, 24 Jun 2004 12:08:50 +0200, Frederic Houbie wrote:
>
> > Hi,
> >
> > I have a problem with ref column.
> >
> > I use these column to link table, I create them with ROWID option.
> > But when I make request with join, it is very slow, I had to create
> > alternate column key to join tables.
> > Any advice with this ?
> I still have to install a 10g here, but looked through the doco, and
> didn't find a reference to a "ROWID option" at column creation. Thus I
> think you meant that you used the ROWID datatype when creating the column
> (i.e alter table mytable add mycolumn ROWID).
> If this is indeed what you did, then you just created a column with a
> datatype that match the virtual column rowid. You surely declared your FK
> as "references anothertable(rowid)".
> First, I don't know if the "rowid" column is indexed or not (gotta check
> that soon, it may be interesting). But if you didn't add an index on your
> FK column, and we both know Oracle won't do it for you, then it's probably
> why you had such poor performances on your join.
>
> Not that you should create an index for each and every FK you stumble
> upon, as for low volume tables, the full table scan is often cheaper (and
> the CBO usually know pretty well about that!).

The decision as to whether to create an index on a foreign key has nothing at all to do with the optimiser's propensity to use full table scans or index access to retrieve data. It is in fact a locking issue. Without an index on the foreign key, and update to the parent key causes the entire child table to be locked for the duration of the transaction. With an index on the foreign key, the parent key update takes a different kind of lock which does not interfere with the ability to perform DML on the child table.

Regardless of the size of tables involved, therefore, the advice should be: if you do DML on the parent table at all, and if you ever modify the parent key of a foreign key constraint, then index the foreign key. For concurrency issues, not data retrieval ones.

None of which has anything to do with what the original poster asked about, so apologies for hijacking his thread.

Regards
HJR Received on Sun Jun 27 2004 - 16:28:18 CDT