SQL – Reformatting to Improve Performance 2

December 4, 2009

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

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:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/d4e3f3f275ed894d

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?

——————————–