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
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?