Do foreign key constraints influence query transformations in Oracle?

Question

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)

Minimum

Answer 1

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.

Atmometer

I was sure I've read something like this before. It seems obvious anyway. Thanks for the great answer and link! – Minimum

Broken link, I guess presentation is this one: docslide.us/documents/metadata-matters-by-tom-kyte-oracle.html – Clarino

Answer 2

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.

Minimum