December 4, 2009
Note: the Oracle cost-based query optimizer is able to automatically transform queries submitted by applications to produce an equivalent SQL statement that will hopefully improve SQL execution performance. The possible transformations are limited by the Oracle release version, the value of the OPTIMIZER_FEATURES_ENABLE parameter (and various other hidden parameters), the potential presence of NULL values, and hints embedded in the SQL statement.
Some time ago the following question appeared in a forum:
The following query is performing poorly in the app. I have tried the scalar subquery approach (using formatted to_char) with no luck. any help would be appreciated.SELECT MP_NO, MP_DESC, MP_ACTIVE AS "FLAG_YES_NO", ( ( SELECT COUNT(*) FROM ST, STINV WHERE ST.ST_ID = STINV.ST_ID AND ST.ST_POSTED IS NOT NULL AND STINV.MP_NO = MPT.MP_NO ) + ( SELECT COUNT(*) FROM PT, PTINV WHERE PT.PT_ID = PTINV.PT_ID AND PT.PT_POSTED IS NOT NULL AND PTINV.MP_NO = MPT.MP_NO ) ) FROM MPT WHERE COMP_ID = 1 GROUP BY MP_NO, MP_DESC, MP_ACTIVE
It might be interesting to see how the performance and DBMS_XPLAN of your SQL statement compares with the following:
SELECT MPT.MP_NO, MPT.MP_DESC, MPT.MP_ACTIVE "FLAG_YES_NO", NVL(S.V1,0) + NVL(P.V2,0) FROM MPT, (SELECT STINV.MP_NO, COUNT(*) V1 FROM ST, STINV WHERE ST.ST_ID = STINV.ST_ID AND ST.ST_POSTED IS NOT NULL GROUP BY STINV.MP_NO) S, (SELECT PTINV.MP_NO, COUNT(*) V2 FROM PT, PTINV WHERE PT.PT_ID = PTINV.PT_ID AND PT.PT_POSTED IS NOT NULL GROUP BY PTINV.MP_NO) P WHERE MPT.COMP_ID=1 AND MPT.MP_NO=S.MP_NO(+) AND MPT.MP_NO=P.MP_NO(+) GROUP BY MPT.MP_NO, MPT.MP_DESC, MPT.MP_ACTIVE;
If possible, the outer join [ (+) ] should be removed from the SQL statement that I posted above.
Is MPT.COMP_ID a column defined as NUMBER?