Re: Slow view needs optimization help

From: Tony Andrews <andrewst_at_onetel.com>
Date: 31 May 2005 06:57:31 -0700
Message-ID: <1117547851.306756.154510@z14g2000cwz.googlegroups.com>

Jon Davis wrote:
> This might be too much hassle for most of you but someone might be up for a
> good challenge. I need to figure out how to optimize this query (at bottom
> of this post) for Oracle 9i.

I can't possibly work on that enormous query, but have you considered using this common approach to "pivot" queries instead?

SELECT A.ACCOUNTID, ...

       SUM( CASE WHEN A.MO = 1 THEN A.QUANTITY END) JAN_QTY,
       SUM( CASE WHEN A.MO = 2 THEN A.QUANTITY END) FEB_QTY,
       ...
       SUM(A.QUANTITY) YR_QTY

FROM C_ACC_SALES A
WHERE ...
GROUP BY A.ACCOUNTID, ...; Received on Tue May 31 2005 - 08:57:31 CDT