A colleague Martin showed me a cool feature of Oracle APEX this week to find missing foreign key indexes. Under SQL Workshop ⟶ Utilities ⟶ Object 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.
Clicking on this report type shows a list of any foreign keys missing an index.
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.
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…