For the query above, the following recommendations will be helpful as part of the SQL tuning process. You'll find 3 sections below:
Description of the steps you can take to speed up the query.
The optimal indexes for this query, which you can copy and create in your database.
An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
Avoid Correlated Subqueries (query line: 12): A correlated subquery is a subquery that contains a reference (column: client_no) to a table that also appears in the outer query. Usually correlated queries can be rewritten with a join clause, which is the best practice. The database optimizer handles joins much better than correlated subqueries. Therefore, rephrasing the query with a join will allow the optimizer to use the most efficient execution plan for the query.
Avoid Selecting Unnecessary Columns (query line: 6): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
Avoid Subqueries (query line: 5): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
The optimized query:
AND a.client_no || ':' || a.engagement_no || ':' || a.registered_date = (
b.client_no || ':' || b.engagement_no || ':' || MAX(b.registered_date)
ON e.client_no = b.client_no
AND e.engagement_no = b.engagement_no
ON c.client_no = b.client_no
AND b.client_no = a.client_no
AND b.engagement_no = a.engagement_no