We are regularly developing forms based applications for filling the business gap with Oracle ERP R12. Most of the applications are developed using Oracle Public APIs and recently we started facing significant slowness with those custom applications and after some research found that, most of the custom application tables were not having indexes.
We gained significant responsiveness once after defining indexes for the custom tables, and much of the PL/SQL sequences started responding 100s of times faster compared to the earlier experiences.
Right now, I would like to know something pretty basic. Other than the PL/SQL execution responsiveness, does the forms application itself get benefited by indexes? I had one through the oracle documentation for indexes, however the documentation hardly points anywhere which explains the forms based applications performance gains through indexes (Once again I am not ignoring the fact that the core of forms based application is mostly based on the fine tuned PL/SQL codes)
My question is based on a real time scenario, where I don't have any PL/SQL sequence scans tables, gathers certain information etc, however, just do a master-detail query. Having indexes on the mater and details tables will enhance the application performance in this scenario?