To create an index on all foreign keys has two benefits:
- Reduced risk of table locks.
- Potentially better performance.
The drawbacks can be neglected. For more information see the discussion in "Ask Tom".
Tom Kyte has published a SQL script to detect unindexed foreign key. It's included beyond with a few amendments to run it in SQL*Plus:
Based upon Toms script, you can easily create another script to build indexes on the unindexed foreign keys using the following code snippet: