Re: Slow Query

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Fri, 01 Jun 2007 19:44:46 -0700
Message-ID: <1180752286.996578.151130@k79g2000hse.googlegroups.com>

On Jun 1, 5:26 pm, hgha <hou..._at_globe-tekcorp.com> wrote:
> Hello,
>
> 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
>
> Thanks,

In addition to Daniel Morgan's request for specific information, 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?

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Jun 01 2007 - 21:44:46 CDT