Re: Slow Query

hgha 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,

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