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! :)
