urgent-query performance

[Sep2013]

Hi guys, I am using the following query, bt it takes long time to give results. Please let me know if i can improve its performance somehow.

SELECT   SUM (WO_COSTS),
         SUM (DIRECT_COSTS),
         SUM (WO_CAPITALISED),
         SUM (DIRECT_CAPITALISED),
         SUM (REVENUE),
         ACCOUNT_CODE
         from (
SELECT   /*+ PARALLEL(A4, 8) PARALLEL(A5, 8)  */
                    SUBSTR (A4.ACCOUNT_CODE, 13, 3) ACCOUNT_CODE,
                    SUM ((CASE
                       WHEN (    SUBSTR (A4.ACCOUNT_CODE, 13, 3) >= '000'
                             AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) <= '999'
                             AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) NOT LIKE '0%%'
                             AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) NOT LIKE '1%%'
                             AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) <> '698'
                             AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) <> '690'
                             AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) <> '691'
                             AND A5.ACCOUNT_CODE IS NOT NULL
                            ) THEN A4.TRAN_AMOUNT
                       ELSE 0
                    END)
                    ) WO_COSTS,
                    SUM ((CASE
                       WHEN (    SUBSTR (A4.ACCOUNT_CODE, 13, 3) >= '000'
                             AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) <= '999'
                             AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) NOT LIKE '0%%'
                             AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) NOT LIKE '1%%'
                             AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) <> '698'
                             AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) <> '690'
                             AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) <> '691'
                             AND A5.ACCOUNT_CODE IS NULL
                            ) THEN A4.TRAN_AMOUNT
                       ELSE 0
                    END)
                    )DIRECT_COSTS,
                    SUM ((CASE
                       WHEN (SUBSTR (A4.ACCOUNT_CODE, 13, 3) ='698' AND A5.ACCOUNT_CODE IS NOT NULL) THEN A4.TRAN_AMOUNT
                       WHEN (SUBSTR (A4.ACCOUNT_CODE, 13, 3) ='690' AND A5.ACCOUNT_CODE IS NOT NULL) THEN A4.TRAN_AMOUNT
                       WHEN (SUBSTR (A4.ACCOUNT_CODE, 13, 3) ='691' AND A5.ACCOUNT_CODE IS NOT NULL) THEN A4.TRAN_AMOUNT
                       ELSE 0
                    END)
                    ) WO_CAPITALISED,
                    SUM ((CASE
                       WHEN (SUBSTR (A4.ACCOUNT_CODE, 13, 3) = '698'  AND A5.ACCOUNT_CODE IS NULL ) THEN A4.TRAN_AMOUNT
                       WHEN (SUBSTR (A4.ACCOUNT_CODE, 13, 3) = '690'  AND A5.ACCOUNT_CODE IS NULL ) THEN A4.TRAN_AMOUNT
                       WHEN (SUBSTR (A4.ACCOUNT_CODE, 13, 3) = '691'  AND A5.ACCOUNT_CODE IS NULL ) THEN A4.TRAN_AMOUNT
                       ELSE 0
                    END)
                    )DIRECT_CAPITALISED,
                    SUM ((CASE
                       WHEN SUBSTR (A4.ACCOUNT_CODE, 13, 3) LIKE '1%%' or
                            SUBSTR (A4.ACCOUNT_CODE, 13, 3) LIKE '0%%' THEN A4.TRAN_AMOUNT
                       ELSE 0
                    END)
                    )REVENUE
               FROM hdwu.MSF90001 A4,
                    hdwu.MSFX9901 A5
              WHERE
                 A4.DSTRCT_CODE = A5.DSTRCT_CODE(+)
                AND A4.PROCESS_DATE = A5.PROCESS_DATE(+)
                AND A4.TRANSACTION_NO = A5.TRANSACTION_NO(+)
                AND A4.REC900_TYPE = A5.REC900_TYPE(+)
                AND A4.USERNO = A5.USERNO(+)
AND  A4.DSTRCT_CODE = 'RPDD'
                AND A4.POSTED_STATUS NOT IN ('U','N')
                AND A4.FULL_PERIOD <= #prompt('EndPeriodPrompt','string')#     
                AND SUBSTR (A4.ACCOUNT_CODE, 5, 1) = '3'
                AND A5.DSTRCT_CODE(+) = 'RPDD'
                AND A5.FULL_PERIOD(+) <= #prompt('EndPeriodPrompt','string')#     
           GROUP BY SUBSTR (A4.ACCOUNT_CODE, 13, 3)
           )
GROUP BY ACCOUNT_CODE

[blom0344]

This a Cognos and not an SQL forum, but basically there are a couple of things to point out:

1. using like and especially 'not like'
2. use of all outer join strategy
3. possibly using Oracle outer join syntax

Which version of Oracle are you working against?

If your DBA analyzes this , then you will see range scan after range scan.  (instead of index scans) 

An Oracle DBA should comment on this

Last question  Why hardcode the summaries and why not build a real model?