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
> 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,
To help you we need the following: 1. DDL for column data types 2. Oracle version number to three decimal places 3. Explain Plan creating using DBMS_XPLAN not a legacy script 4. A definition of "poorly" specifically referencing your SLA -- Daniel A. Morgan University of Washington damor...@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org
> 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.
> On Jun 1, 5:26 pm, hgha <hou...@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 -
