Home » SQL & PL/SQL » SQL & PL/SQL » Performance issues
Performance issues [message #259196] Tue, 14 August 2007 11:17 Go to next message
riyan
Messages: 2
Registered: August 2007
Junior Member
hi All,
i have a query as follows. the query main1 is a history table and has data fopr 104 weeks. please can you advice is there any way to improve the performance of this query.


DECLARE

v_endwk NUMBER(8);
v_startwk NUMBER(8);

cursor week_cur is select distinct week_no1, week_no2 from tt02;


begin

for week_rec in week_cur
loop
v_endwk := week_rec.week_no1;
v_startwk := week_rec.week_no2;

insert into tt03 (STIT_NUMB,STR_NO,FULL_WEEK,SALES_VALUE,TOTAL_WASTE)
SELECT
X.STIT,
Z.STR,
Y.WEEK,
SUM(Y.SALES) SALES,
SUM(Y.WASTE) WASTE
FROM
a X,
main1 Y,
b Z,
TT02 PERF
WHERE
Y.BITM= X.BITM
AND Y.ORGU= Z.ORGU
AND X.STIT=PERF.STIT
and PERF.week_no1 = v_endwk
and perf.week_no2 = v_startwk
AND Y.WEEK BETWEEN v_endwk AND v_startwk
GROUP BY
X.STIT, Z.STR, Y.WEEK ;

END LOOP;

commit;

end;


Regards,
riyan.
Re: Performance issues [message #259205 is a reply to message #259196] Tue, 14 August 2007 11:38 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
How about posting formatted TKPrOf and explain plan results
Re: Performance issues [message #259209 is a reply to message #259196] Tue, 14 August 2007 11:46 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not directly using "insert ... select ..." instead of PL/SQL procedure? Then you will have better performances.

Regards
Michel
Previous Topic: How to display message using default lock in ORACLE 9i
Next Topic: sun of bytes
Goto Forum:
  


Current Time: Fri Dec 09 21:45:21 CST 2016

Total time taken to generate the page: 0.18804 seconds