On Jun 1, 10:44 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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_ACTIVEAS "FLAG_YES_NO",
> > (
> > (SELECTCOUNT(*) FROM ST, STINV
> > WHERE ST.ST_ID = STINV.ST_ID
> > AND ST.ST_POSTED IS NOT NULL
> > AND STINV.MP_NO= MPT.MP_NO)
> > +
> > (SELECTCOUNT(*) 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 BYMP_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.- Hide quoted text -
>
> - Show quoted text -
Thank you Charles. Your query works much faster.
Received on Tue Jun 05 2007 - 14:37:53 CDT
