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

  • in sql server,it help to produce better query plan,if FK is trusted. – KumarHarsh Apr 18 at 11:36
  • 1
    Take a look at my long answer here! No. 2 in Jonathan Lewis' list of database disasters is trying to enforce FOREIGN KEY integrity yourself! An index not only gives you a better plan but it also improves delete speed on the parent table! Obviously it will have a small impact on INSERTs, but this drawback is far outweighed by the benefits of FOREIGN KEYs. – Vérace Apr 18 at 12:14
  • 2
    Yes. One feature that can benefit from FKs is table elimination. See Oracle docs: docs.oracle.com/database/121/TGSQL/… and various articles on the Internet: google.com/search?q=oracle+table+elimination – ypercubeᵀᴹ Apr 18 at 12:48
up vote 7 down vote accepted

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

  • So yes, it can improve performance! A good example, but there are also other tricks the optimizer can do. Hope folks realize the impact on performance is usually positive (usually better query plans), and also it eliminates you from enforcing constraints in YOUR code; let the database do its job! – Mark Stewart Apr 18 at 18:31

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.