Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Do foreign key constraints influence query transformations in Oracle?

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)

like image 589
Lukas Eder Avatar asked Nov 16 '11 14:11

Lukas Eder


People also ask

Does foreign key improve query performance?

Foreign key indexes can significantly improve performance for queries that involve joins between the parent and child tables.

What do foreign key constraints do?

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.

Why foreign key is not recommended?

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.

Does foreign key reduce performance?

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.


2 Answers

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.

like image 92
Justin Cave Avatar answered Oct 11 '22 18:10

Justin Cave


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:

  • JOIN Elimination: An Essential Optimiser Feature for Advanced SQL Usage
  • 10 Cool SQL Optimisations That do not Depend on the Cost Model

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.

like image 23
Lukas Eder Avatar answered Oct 11 '22 18:10

Lukas Eder