Finding & Fixing Unindexed Foreign Keys

A colleague Martin showed me a cool feature of Oracle APEX this week to find missing foreign key indexes. Under SQL WorkshopUtilitiesObject Reports, a number of helpful reports offer insights about your application’s database objects. As shown by the arrow in the figure below, one of these is the Unindexed Foreign Keys exception report.

SQL Workshop ⟶ Utilities ⟶ Object Reports Page

Clicking on this report type shows a list of any foreign keys missing an index.

SQL Workshop ⟶ Utilities ⟶ Object Reports ⟶ Unindexed Foreign Keys Report

This useful result highlighted a number of opportunities to potentially improve the performance of my application. However, as I continue to find, Oracle APEX has an ace up its sleeve. Notice the (Create Script) button in the report toolbar. Clicking on that button created the SQL script shown below to create all the missing indexes.

Automatically created SQL script to create missing foreign key indexes

One additional click on the (Run) button and all my missing indexes were a thing of the past!

As with so many things I’m discovering about Oracle APEX, getting things done ’tis but the work of a moment as Rowan Atkinson’s store clerk character said in a family favorite film Love Actually