Do foreign keys affect performance in Oracle?
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 &lt;= 100;

insert into t2
  select rownum, t1_id 
  from   t1, (
    select * from dual connect by level &lt;= 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! :)