Re: REF column and index

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<40df3bd3$0$16106$afc38c87_at_news.optusnet.com.au>...
> "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
Hi Howard,

   Dont know why you are saying that data retrieval is not an issue since in case of joins between master and detail tables the detail table would be full scanned if you do not have an index on the foreign key.It may not be desirable in case of high data volumes even though you do not do any DML on the parent.
Also I think 9i onwards the child table is locked only for the duration of the execution of the DML statement rather than the whole transaction.

Regards,
Kal Received on Mon Jun 28 2004 - 07:41:53 CDT