Slow Query

[hgha]

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,

[DA Morgan]

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
damo...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[Charles Hooper]

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.

[hgha]

On Jun 1, 10:44 pm, Charles Hooper <hooperc2...@yahoo.com> wrote:
> 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 )

> K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> - Show quoted text -

Thank you Charles. Your query works much faster.