Do I need to create indexes on foreign keys on Oracle?

The foreign key constraint alone does not provide the index on Oracle - one must (and should) be created.


Creating a foreign key does not automatically create an index on A.B_ID. So it would generally make sense from a query performance perspective to create a separate index on A.B_ID.

If you ever delete rows in B, you definitely want A.B_ID to be indexed. Otherwise, Oracle will have to do a full table scan on A every time you delete a row from B to make sure that there are no orphaned records (depending on the Oracle version, there may be additional locking implications as well, but those are diminished in more recent Oracle versions).