Regardless of whether you create indexes for your foreign keys (which if course you should), does having foreign keys have any impact on performance in Oracle databases?
One specific area i was wondering about was whether they help the database produce better query plans
Best Answer
Yes.
The optimizer can remove redundant tables from a query when RI is enforced in the database.
For example, here are two tables:
The second only contains t1_ids from the first:
So a count of T2 returns the same number of rows as a join of the two tables:
But there's no FK defined :(
So the optimizer doesn't know this. And it needs to access both tables when executing the query:
But add a foreign key to the mix:
And it now knows there can't be any t1_id values in t2 that don't exist in t1. So it can ignore t1:
Poof! It's gone! :)