tuning the procedure with sql

[engiri]

declare
CURSOR C3 IS SELECT PS_SHOP_CODE,PS_CONS_DATE,
TO_NUMBER(TO_CHAR(PS_CONS_DATE,'YYYYMM'))YEAR_MON,PS_PLU_CODE,
SUM(NVL(DECODE(PS_CONS_TYPE,'R',(-PS_PLU_QTY),PS_PLU_QTY),0)) QTY,
SUM((NVL(DECODE(PS_CONS_TYPE,'R',(-PS_VALUE),PS_VALUE),0)) -
(NVL(DECODE(PS_CONS_TYPE,'R',(-PS_DISCOUNT),PS_DISCOUNT),0)) -
(NVL(DECODE(PS_CONS_TYPE,'R',(-PS_TAX1AMT),PS_TAX1AMT),0)) -
(NVL(DECODE(PS_CONS_TYPE,'R',(-PS_TAX2AMT),PS_TAX2AMT),0))-
(NVL(DECODE(PS_CONS_TYPE,'R',(-PS_TAX3AMT),PS_TAX3AMT),0))) SAL_VAL
FROM TEMP_CONS_DAY GROUP BY PS_SHOP_CODE,PS_CONS_DATE,TO_NUMBER(TO_CHAR(PS_CONS_DATE,'YYYYMM')),PS_PLU_CODE;
begin
for i in c1 loop
UPDATE STOKCUMM SET STCU_SAL_VALUE=NVL(STCU_SAL_VALUE,0) +
NVL(i.SAL_val,0) WHERE STCU_ITEM_CODE = i.PS_PLU_CODE
AND YEAR_MON = 20001 AND STCU_BRAN_CODE = 'XXX' ;
END LOOP;
END;
/


Some of the required details.

Stokcumm table is having 4millian records of the current year. currently stcu_item_code and year_mon(12 values per year) columns are having indexes.

The cursor is taking around 8000 records.

1 Will it give performance gain if I write theabove in a single
statement?
2.Am I need to remove the index on year_mon column.

3.Any other hints on further improving the performance of the above.

Thanks

[anandkl]

What is the total no of records in TEMP_CONS_DAY, is there an index on ps_plu_code.

regards
anandkl

[engiri]

Around 12000.

There is no index on that field.. Is it required..?

[anandkl]

Its not required

[engiri]

My questions is writing that in a single sql statement
with all the functions will gain performance or not.

[anandkl]

Hi,
include commit in your loop.
can u please show us the execution plan of the UPDATE statement

regards
anandkl

[abhaysk]

Originally posted by engiri
1 Will it give performance gain if I write theabove in a single
statement?

Thanks

Yes

[tamilselvan]

Always a single SQL statement works faster than PL/SQL row processing. Go for it.

Tamil