Foreign Keys and indexes

 

In this test case we are going to use two tables with a foreign key.  The question is, if we create a foreign key, do we need to add an index on that foreign key column?

OPS$ORACLE@VETDEV > create table bigtable as select * from dba_objects;

Table created.

OPS$ORACLE@VETDEV > create table owners as select * from dba_users;

Table created.

OPS$ORACLE@VETDEV > analyze table bigtable compute statistics;

Table analyzed.

OPS$ORACLE@VETDEV > analyze table owners compute statistics;
Table analyzed.

OPS$ORACLE@VETDEV > alter table bigtable add primary key (object_id);

Table altered.

OPS$ORACLE@VETDEV > alter table owners add primary key (username);

Table altered.

OPS$ORACLE@VETDEV > alter table bigtable add constraint bigtable_fk1
  2* foreign key (owner) references owners(username);
Table altered.

We are going to write a query against both tables joining them on the foreign key. Start by setting autotrace on with explain and statistics.  In the following explain plan we are cutting out statistics to focus on what jumps out at us. Even though we have a foreign key constraint we still got a full table scan on both big table and owners.

TC1

Okay, lets try and limit the number of rows returned by adding a filter predicate.

set autotrace trace

select b.owner, b.object_type, o.account_status
from owners o
,   bigtable b
where o.username = b.owner
and b.owner = ‘SUZANNE’;

Now first you will notice we put the filter predicate on b.owner and not o.username.  We know from the data distribution we have multiple owners and unique usernames. Now what does the explain plan look like.

image
I’m about to get off on a tangent; but bare with me.  There is something interesting going on here.  Look at the index unique scan against the username primary key column and the filter predicate on bigtable.owner.  What happened?  We are going to use the 10053 trace to find out what the optimized did.

alter session set events ‘10053’;

set autotrace trace

select b.owner, b.object_type, o.account_status
from owners o
,   bigtable b
where o.username = b.owner
and b.owner = ‘SUZANNE’;

Lets find the trace file we just generated and dig through it.  The trace file VETDEV_mmon_6474.trc will tell us just what the optimizer did.

[oracle@owirdb1 trace]$ ls -ltr
total 600
-rw-r—– 1 oracle oinstall 594496 Jan 16 07:00 alert_VETDEV.log
-rw-r—– 1 oracle oinstall    384 Jan 16 12:02 VETDEV_mmon_6474.trm
-rw-r—– 1 oracle oinstall   4111 Jan 16 12:02 VETDEV_mmon_6474.trc
[oracle@owirdb1 trace]$

There is a bunch of information in this file and it’s beyond the scope of what I wanted to discuss; what we are looking for is how did the optimizer transform our query.

**************************
Predicate Move-Around (PM)
**************************
PM:     PM bypassed: Outer query contains no views.
PM:     PM bypassed: Outer query contains no views.
query block SEL$1 (#0) unchanged
FPD: Considering simple filter push in query block SEL$1 (#0)
“O”.”USERNAME”=”B”.”OWNER” AND “B”.”OWNER”=’SUZANNE’
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
finally: “O”.”USERNAME”=”B”.”OWNER” AND “B”.”OWNER”=’SUZANNE’ AND “O”.”USERNAME”=’SUZANNE’

Check this out, the optimizer rewrote the query to reduce the number of rows returned in the filter.

OPS$ORACLE@VETDEV > select count(*) from owners where username = ‘SUZANNE’;

  COUNT(*)
———-
         1

OPS$ORACLE@VETDEV > select count(*) from bigtable where owner = ‘SUZANNE’;

  COUNT(*)
———-
       199

So it makes more sense to return one row from owners and using that for the filter as apposed to using owners from bigtable.

Well what would happen if we put an index on the foreign key?

OPS$ORACLE@VETDEV > create index bigtable_idx1 on bigtable(owner);

Index created.

OPS$ORACLE@VETDEV > analyze index bigtable_idx1 compute statistics;

Index analyzed.

Now lets run the test again and without the filter predicate.
TC1
Same issue, but why. Well lets look at the query and ask what is getting returned.  Well, there are no filter predicates, so every row is going to be returned.  This is shown under predicate information and the rows column.  Therefore in this case, the full table scan with a hash join is the most efficient way to get the data. 
What would happen if we add a filter predicate to the query?
TC1
Well that’s nice, we have the index range scan on bigtable_idx1 and an index unique scan on the owners primary key index. 
So, what did we learn, 1) not having an index on the foreign key pretty much guarantees a full table scan on the joined table. 2) having an index does not guarantee the index will be used.  If the database determines that it would be cheaper to do a full table scan then that is what it’s going to do. 3) a filter predicate to the query reduced the total number of rows returned to the point where it would be cheaper to do an index unique scan. And 4) the optimizer will re-write a query to improve performance.
This entry was posted in Database Stuff by rlockard. Bookmark the permalink.

About rlockard

Robert Lockard is a professional Oracle Designer, Developer and DBA working in the world of financial intelligence. In 1987 his boss called him into his office and told him that he is now their Oracle Wizard then handed him a stack of Oracle tapes and told him to load it on the VAX. Sense then, Robert has worked exclusively as an Oracle database designer, developer and Database Administrator. Robert enjoys flying vintage aircraft, racing sailboats, photography, and technical diving. Robert owns and fly’s the “Spirit of Baltimore Hon” a restored 1948 Ryan Navion and lives in Glen Burnie Maryland on Marley Creek

Welcome to oraclewizard