I have a situation like this:
create table a(
a_id number(38) not null,
constraint pk_a primary key (id)
);
create table b(
a_id number(38) not null
);
create index b_a_id_index on b(a_id);
Now b.a_id
is in fact meant to be a foreign key referencing a.a_id
, but it isn't formally declared as such. Obviously, it should be for integrity reasons. But does a foreign key constraint also improve join performance in general or in specific cases? If yes, for what types of query transformations?
Is there any relevant documentation about this topic?
I'm using Oracle 11g (11.2.0.2.0)
Foreign key indexes can significantly improve performance for queries that involve joins between the parent and child tables.
The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables. A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.
Having active foreign keys on tables improves data quality but hurts performance of insert, update and delete operations. Before those tasks database needs to check if it doesn't violate data integrity. This is a reason why some architects and DBAs give up on foreign keys at all.
It's a common mistake to avoid creating foreign keys in a database because they negatively impact the performance. It is true that foreign keys will impact INSERT, UPDATE and DELETE statements because they are data checking, but they improve the overall performance of a database.
Yes, having foreign key constraints in place can improve query performance. There are various transforms that are open to the optimizer when appropriate foreign key constraints exist that are not generally available. For example, if you were to join A
and B
but only select data from B
, the optimizer could eliminate A
from the query plan entirely if there was a foreign key constraint in place (this sort of thing comes in very handy when you've got useful views that join in more tables than your current query strictly needs because you don't have to trade the performance costs of the extra joins against the code reuse from using an existing view). They also come in handy when you're doing things like using things like query rewrite to rewrite a query to use a materialized view in a data warehouse/ DSS type system.
Tom Kyte has a presentation Metadata Matters that talks about how various types of constraints, along with other pieces of metadata, can influence the optimizer.
As Justin already pointed out, JOIN elimination is an essential non-cost based SQL transformation, which can be applied based on the presence of meta data only. I have blogged about this more recently:
As I originally assumed, there are a lot of SQL transformations that depend on meta data, so adding foreign key constraints (and other constraints) definitely can impact performance in a positive way.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With