Re: REF column and index

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Mon, 28 Jun 2004 23:07:18 +1000
Message-ID: <40e017e8$0$18190$afc38c87@news.optusnet.com.au>

"Kalmact" <kalmact_at_hotmail.com> wrote in message news:8007e332.0406280441.431784eb_at_posting.google.com... > "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

I didn't say that. I said that the decision as to whether to index a foreign key is not a matter of data retrieval but of locking.

> 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.

The point is, the earlier poster said 'don't create an index on a foreign key for low volume tables' (by which I presume he meant small ones). Small tables will likely be full-scanned even with an index on the foreign key. So why bother indexing it? Because although the index won't help the data retrieval method, it will still prevent the locking issue, which is the main performance concern.

>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.

Oh dear. Tell me you haven't bought into that hoary old myth that says the foreign key locking issue has gone away in 9i? It isn't true. Yes, the update on the parent will release the lock on the child table fairly immediately, *assuming it has managed to acquire it in the first place*. But if there is pre-existing DML on the child table, then the parent DML will not be able to acquire the lock it needs in the first place. It will therefore hang, waiting for it. And *every other piece of CHILD table DML will queue up behind it*. The locking issues in 9i are just as bad as they ever were in 8i or earlier, and it is only marketing hype that ever suggested otherwise.

So my point still stands. It is not primarily a data retrieval issue ("If I index the foreign key, things will go faster"). It is a locking issue ("If I don't index the foreign key, things will come to a complete, grinding halt because of exclusive table locks"). The index may or may not improve data retrieval performance (depends on whether the table is big or small, read anyway by a FTS or not). But the index will *always* improve concurrency-busting locking issues.

Regards
HJR

>
> Regards,
> Kal

Received on Mon Jun 28 2004 - 08:07:18 CDT