Oracle foreign key index support means always having an index on your foreign key columns. 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:
The index helps optimize a query such as the following:
As we can see from the explain plan, the index is used to filter out the SA_REP rows:
Let’s see what happens if we were to drop this index and re-execute the query:
Now the constraint shows nothing under “Index support”:
Now, when we re-execute the query, note the new Explain Plan showing a vastly increased 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:
The dialog comes up with the necessary information for the highlighted row filled in.
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.
We hope you agree with as, Oracle foreign key index support is important to consider.
Explore possibilities of KeepTool.
