Select Statement

[dorist8]

Hi,

I have the following select statement in a package. The problem is that it takes from 39 minutes to an hour to complete. Do anyone have any suggestions to maybe make the select run faster. I am using the select as part of an insert statement.


INSERT INTO twpcir.TWP_SUBS_GL_ACCTS(COMPANY_CODE, COST_CENTER, END_CODE, FIN_CODE1, FIN_CODE2,
DEBIT_AMT, CREDIT_AMT, DESCRIPTION, CODE)
SELECT '500' COMPANY_CODE,
(case when ((trim(code) = 'RECC') and (TRIM(ACCOUNT) = 'No G/L Account')) Then '000000' else
SUBSTR(TRIM(ACCOUNT),1,6) end) COST_CENTER,
(case when ((trim(code) = 'RECC') and (TRIM(ACCOUNT) = 'No G/L Account')) Then '105560' else
SUBSTR(TRIM(ACCOUNT),7,12) end) END_CODE,
'000000' FIN_CODE1, '000000' FIN_CODE2,
(CASE WHEN (TRIM(CR_DR) = 'DR') THEN ROUND(SUM(AMOUNT),2) ELSE 0 END) DEBIT,
(CASE WHEN (TRIM(CR_DR) = 'CR') THEN ROUND(SUM(AMOUNT),2) ELSE 0 END) CREDIT,
TRIM(DESCRIPTION), TRIM(CODE)
FROM VW_SBS_GL_ACCOUNT
WHERE APPLIED_DATE BETWEEN a_gl_start AND a_gl_end
AND (PUBLICATION = 1)
GROUP BY PUBLICATION, GL_REFERENCE, CODE, DESCRIPTION, SUB_TYPE, ACCOUNT,CR_DR;

dorist8

 

[Turkbear]

Hi,
Have you tried to analyze the 'SELECT...' part of your proc with an Explain Plan? That might give you clues as to where the bottlenecks are - How many records are in VW_SBS_GL_ACCOUNT and are the Dates indexed?




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[IanWaterman]

The name of the datasource VW_SBS_GL_ACCOUNT suggests a view.

If it is have you tried converting that to a materialsied view?

Ian