-
tuning the procedure with sql
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
-
What is the total no of records in TEMP_CONS_DAY, is there an index on ps_plu_code.
regards
anandkl
anandkl
-
Around 12000.
There is no index on that field.. Is it required..?
-
-
My questions is writing that in a single sql statement
with all the functions will gain performance or not.
-
Hi,
include commit in your loop.
can u please show us the execution plan of the UPDATE statement
regards
anandkl
anandkl
-
Re: tuning the procedure with sql
Originally posted by engiri
1 Will it give performance gain if I write theabove in a single
statement?
Thanks
Yes
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Always a single SQL statement works faster than PL/SQL row processing. Go for it.
Tamil
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|