If you have a concatenated primary key in the parent table, then the corresponding concatenated foreign key may be unused and a candidate for dropping as discussed. However, if additional fields have been concatenated to the foreign key index, then it is most likely that the index is not "unused" and therefore should not be dropped. Is that what you were asking, or have I misunderstood the question?

One last question:
The foreign key index that is created, can it be the first field of a concatenated index???

The "overhead" of acquiring TM instance locks (enqueues) is orders of magnitude greater in OPS than in single instance Oracle, and can in fact dwarf the remaining costs of executing an OLTP query. Whereas, in single instance Oracle these overheads are tiny, even when a segment is accessed intensively by a large number of clients. You would get contention for the segment header block long before having trouble with the associated DML lock or TM enqueue. I would consider it for a benchmark, but otherwise not.

On the other hand, disabling DML locks to make it safe to remove otherwise unused foreign key indexes is a good idea if the table is subject to significant DML. I would do it progressively. Firstly, disable the DML locks, without dropping the indexes. If you have misunderstood your application, and the DML locks are in fact needed, then they can be enabled again in a trice. Thereafter, drop the unused indexes one at a time, and wait to make sure that all is well before dropping the next one.

I have worked with Parallel Servers before and I know the concept of disabling DML Locks to avoid lock acquisition overhead. My question is:

1. We have a very intensive OLTP system that is being written from many batch Sun clients with multiple threads running per box. I would think that this would be the same as running parallel server option and accessing certain objects from multiple sessions simultaneously. You do mention Parallel Server but don't mention if this is the only reason why you disable DML locks. I would think that this would be a similar reason. Should I do this in this situation???

2. If not for reason #1, I am sure that you agree that to avoid any unnecessary Foreign Key indexes, disabling DML Locks would be a benefit for the Child table inserts, deletes and possible updates of fields which are indexed. I definitely have this problem. I have a table which has 200 Million rows and is growing(Vendor application) with indexes(9) which some fields might become Bitmap Indexes but some are probably not being used(I am about to perform a system trace to find used indexes). But due to the fact that they have foreign keys on those fields, I am guessing that the vendor has attached indexes(or used a Case tool-like Designer- and these tools usually automatically attach indexes to foreign keys). I probably should disable at the table level the DML locks???