Query Optimizations

Provide an example of Query Optimization.

Following is the query to select Audit information along with latest status which in the AuditDetails table.

Naive way:

SELECT AUDIT.*, AUDIT_DTLS.STATUS FROM AUDIT LEFT OUTER JOIN AUDIT_DTLS ON AUDIT_DTLS.AUDIT_ID = AUDIT.IDAND AUDIT_DTLS.ID IN ( SELECT MAX(ID) FROM AUDIT_DTLS max_audit_dtls GROUP BY max_audit_dtls.AUDIT_ID )

In this case, there is JOIN with AUDIT Table and AUDIT_DTLS(huge) table. Thus, in query plan :

There is a table scan on AUDIT Table, Hash Join (because it is indexed) with (because left Join) AUDIT_DTLS table. Full table scan for this table.

Alternate Query is:

SELECT AUDIT.*, e1.STATUS FROM AUDITLEFT OUTER JOIN (SELECT AUDIT_DTLS.ID, AUDIT_DTLS.STATUS, AUDIT_DTLS.AUDIT_ID FROM AUDIT_DTLS WHERE AUDIT_DTLS.ID IN (SELECT MAX(ID) FROM AUDIT_DTLS max_audit_dtls GROUP BY max_audit_dtls.AUDIT_ID ) ) e1 ON AUDIT.ID = e1.AUDIT_ID

The betterment here is that Join is on the subset of AUDIT_DTLS table.