If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

That was true for pre-9i databases and only, if you deleted a parent key (with DELETE CASCADE defined on FK) or if you updated a PK column of the parent that was referenced by child's FK.

But since 9.0, even when any of those two (rather unusual) situation occurs, Oracle doesn't need index on FK to prevent locking of child table. In short, indexes on FKs are not needed any more to prevent locking.

Jurij ModicASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?

That was true for pre-9i databases and only, if you deleted a parent key (with DELETE CASCADE defined on FK) or if you updated a PK column of the parent that was referenced by child's FK.

But since 9.0, even when any of those two (rather unusual) situation occurs, Oracle doesn't need index on FK to prevent locking of child table. In short, indexes on FKs are not needed any more to prevent locking.

Modic, I disagree with your points.
I do not know how you tested.
Here is my test.

The following demo was tested in Oracle 9.2.0.5

The easiest way to check FK Indexes are needed is disable
table lock on the child table.

so what, drop those unused indexes and if you run something like an erp system wait for the end of the quarter of year end when those processes that run only once and awhile and see what happens. Are they realy hurting you? can you prove it?

Almost by definition, dropping objects that are not used is never going to help improve performance. The trouble here is that you're guessing abouyt the cause of your poor performance instead of measuring the system activity.

Gather a 15 minute interval statspack run tas a starting point, and see what the major waits on the system are and what the most resource intensive SQL statements are. You'll then have a starting point for actual analysis, instead of guesswork.

Well, maybe the above command was appropriate for yor demonstration, but in reality this is one of the most obscure command that you'll never find in any of the serious application on the oracle database.

I didn't say oracle doesn't need any lock on the table when you delete the row from the primary that has ON DELET CASCADE on the referencing foreign key. I just said you doesn't have locking isues any more if you don't have forign key indexed. And I still stick to it, despite your demonstration. The fact that you don't need an index on FK to prevent DML operations on child when you delete a row or update a PK column on the parent was one of the more prominent features advertised when Oracle 9i came out. And it wasn't just marketing hype, it realy works at it was advertised.

Because you disabled Oracle from doing it the way it normaly does it with your DISABLE TABLE LOCK command. But in reality you will never use that command, would you? So would you have a locking isues if you don't have an index on that foreign key? No, of course not, if you would allow Oracle to do the things as it was deseigned for.

It is well known that since Oracle 9i, if you delete a row from the parent table and if the foreign key was defined with DELETE CASCADE, Oracle will have to obtain a shared table lock on the child table for a very short period of time, "but then releases it immediately after obtaining it", as it says in the documentation. So yes, it still needs a shared table lock, but only momentarily - after it gets it, it releases it immediately and other transactions are happily permited to perform DML operations on the child table. They arent blocked, they don't have any locking issues at all.

So, in your demonstration, ommit the "ALTER TABLE DISABLE TABLE LOCK" statement and after deleting a row from the parent table, try to insert/update/delete rows from your child table - you'll se that you are permited to do so, even when your FK is not indexed. You have no locking issues on child table whatsoever! That is a big difference compared from pre-9i releases. If you would try to do so with the pre-9i database, your DML on child table will fail, because the delete on the parent table will cause a permanent (for the duration of the parent transaction) lock on the child table.

Now, I didn't say you don't need indexes on FK columns at all in 9i and above. Perhaps your queries need them. Also, if you have an ON DELETE CASCADE and you delete rows from your parent table, you'd probably wan't to have an index on it for performance reasons. What I said was: you don't need them simply because of the locking issues. That was true until 8i, but since 9i you can perform DML operations on the unindexed child table even while the transactions on your parent table are modifying rferenced primary/unique key.