Index support for foreign keys


If your table has a foreign key to a master table, it is always a good idea—although not required—to have an index on the foreign key column to improve performance. If you should find situations where the index is missing, it’s easy to create it with Hora.

Here we’ve cloned the HR schema into HR1 to show how this can be done. There is a foreign-key constraint that requires a job_id value in the EMPLOYEES table to match a primary key in the JOBS table. It is supported by the index EMP_JOB_IX:

1 - Constraints

The index helps optimize a query such as the following:

SQL Query Result

As we can see from the explain plan, the index is used to filter out the SA_REP rows:

explain plan

Let’s see what happens if we were to drop this index and re-execute the query:

drop index

Now the constraint shows nothing under “Index support”:

index support

Now, when we re-execute the query, note the new Explain Plan showing a vastly increased CPU cost.

cpu cost

Another reason for having indexes on foreign key columns is to prevent a full table locks on the child table when there is a change to the parent table’s primary keys. For a complete explanation using the HR.DEPARTMENTS and HR.EMPLOYEES tables, see the Oracle Concepts manual.

If you ever find an example of a foreign key constraint unsupported by an index, it is easy to correct through the “Create index on FK columns” dialog, which can be called up from the context menu on the Constraints tab:

create index FK Columns

The dialog comes up with the necessary information for the highlighted row filled in.

add unique or primary

We have the option of replacing the index name EMP_JOB_FK_IDX with the original Oracle-supplied index name, EMP_JOB_IX, before proceeding.