Hi All,
Help me to improve my query performance
this PL/SQL block takes around 4 minutes to execute
but i want to improve this query performance because it must finish with in seconds
if you have any idea to improve the performance of query,help me

This is my PL\SQL block

declare
Type table_id is table of number index by binary_integer;
v_id table_id;
begin
select elp_id bulk collect into v_id from table1 where sl_id=1500;

forall i in v_id.First..v_id.Last
update table2 set cost=cost+4 where elp_id=v_id(i);
commit;
end;
/


Here table1 having around 1,50,00,000(one and half crore) records
and table2 having around 2,00,000(two lakhs) records

select * from table1 where sl_id=1500;

elp_id sl_id
------- ------
1 1500
124 1500
203 1500
404 1500

1,25,245 records selected

select * from table2 ;

elp_id elp_name cost
------- ---------- ------
1 elp1 58
2 elp2 675
3 elp3 75
4 elp4 8

1,89,345 records selected


Here am not doing anything other then select and update.
i created index for elp_id(table1), elp_id(table2), sl_id(table1)


Thanks in advance for your thoughts,

Mani

Hi Mani.

>> update table2 set cost=cost+4 where elp_id=v_id(i);

What percentage of the table rows are you updating?

If you update most of the table rows, you can use CTAS to improve update speed:

http://www.dba-oracle.com/t_efficient_upda...ql_dml_tips.htm


*************************************************************
You can also use faster storage, and it would complete in well under a second:

http://www.dba-oracle.com/oracle_news/2006..._disk_speed.htm

With SSD, you can easily do 300,000 updates per second . . . .

*************************************************************
>> 2,00,000(two lakhs)

Huh? That's not a number. What's a lakhs?? This is an English forum!