Do foreign keys affect performance in Oracle?

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

Yes.

The optimizer can remove redundant tables from a query when RI is enforced in the database.

For example, here are two tables:

create table t1 (
  t1_id int not null primary key
);

create table t2 (
  t2_id int not null primary key,
  t1_id int not null
);

The second only contains t1_ids from the first:

insert into t1
  select level from dual 
  connect by level <= 100;

insert into t2
  select rownum, t1_id 
  from   t1, (
    select * from dual connect by level <= 10
  );

commit;

So a count of T2 returns the same number of rows as a join of the two tables:

select count(*) from t2;

COUNT(*)   
      1000 

select count(*)
from   t1
join   t2
on     t1.t1_id = t2.t1_id;

COUNT(*)   
      1000

But there's no FK defined :(

So the optimizer doesn't know this. And it needs to access both tables when executing the query:

set serveroutput off

select /*+ gather_plan_statistics */count(*)
from   t1
join   t2
on     t1.t1_id = t2.t1_id;

COUNT(*)   
      1000 

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                                
SQL_ID  8p235qbxm8yn0, child number 0                                                            
-------------------------------------                                                            
select /*+ gather_plan_statistics */count(*) from   t1 join   t2 on                              
t1.t1_id = t2.t1_id                                                                              

Plan hash value: 3484656271                                                                      

----------------------------------------------------------------------------------------------   
| Id  | Operation           | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |   
----------------------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT    |              |      1 |        |      1 |00:00:00.01 |      10 |   
|   1 |  SORT AGGREGATE     |              |      1 |      1 |      1 |00:00:00.01 |      10 |   
|   2 |   NESTED LOOPS      |              |      1 |   1000 |   1000 |00:00:00.01 |      10 |   
|   3 |    TABLE ACCESS FULL| T2           |      1 |   1000 |   1000 |00:00:00.01 |       6 |   
|*  4 |    INDEX UNIQUE SCAN| SYS_C0014412 |   1000 |      1 |   1000 |00:00:00.01 |       4 |   
----------------------------------------------------------------------------------------------   

Predicate Information (identified by operation id):                                              
---------------------------------------------------                                              

   4 - access("T1"."T1_ID"="T2"."T1_ID")

But add a foreign key to the mix:

alter table t2 add constraint fk foreign key ( t1_id ) references t1 ( t1_id );

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:

select /*+ gather_plan_statistics */count(*)
from   t1
join   t2
on     t1.t1_id = t2.t1_id;

COUNT(*)   
      1000 

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                       
SQL_ID  8p235qbxm8yn0, child number 0                                                   
-------------------------------------                                                   
select /*+ gather_plan_statistics */count(*) from   t1 join   t2 on                     
t1.t1_id = t2.t1_id                                                                     

Plan hash value: 476902662                                                              

-------------------------------------------------------------------------------------   
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |   
-------------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |       6 |   
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |       6 |   
|   2 |   TABLE ACCESS FULL| T2   |      1 |   1000 |   1000 |00:00:00.01 |       6 |   
-------------------------------------------------------------------------------------

Poof! It's gone! :)

StackExchange Database Administrators Q#204314, answer score: 9