Indexes increase the amount of work that the database has to do for DML operations. If a foreign key index does not support an important access path, and if there are normally no deletes or primary key updates on the parent table, then the index is unwanted. It is only needed to protect against a table lock being taken during unexpected DML against the parent table. That protection can also be obtained by disabling table locks against the child table, without the unwanted performance overhead of the index. There is no integrity concern. The unexpected DML will just fail with an ORA-00069 error and roll back.

Although this feature was designed for parallel server, there is no reason why it should not be used in single instance Oracle. Indeed, I believe that it is best practice to design applications to run without table locks routinely, and only to enable them for maintenance operations as required.

I always thought that FK should be indexed to avoid full table lock - full stop. The Oracle says disabling table lock is only for a parallel server scenario. Why do you say disabling full table lock is the better way ? Is there any integrity concern if full table lock is diabled ?