Optimising the nested loop. [message #38313] |
Tue, 09 April 2002 21:20 |
Mahesh
Messages: 90 Registered: January 2001
|
Member |
|
|
Hellow,
I need to find out the difference of each record in a table with the rest of the records in the table. For this purpose, i have written foloing nested program.
cursor c1 is select * from temp;
cursor c2 is select * from temp;
for rec1 in c1 loop
diff := 0;
for rec2 in c2 loop
diff := diff + diff(rec1,rec2)
end loop;
dbms_output.put_line(diff);
end loop;
The problem is that is program is very very slow. With 500 records, it takes more than 1 minute which is not acceptable for me.
I am wondering whether i can optimize this program. I am thinking that the cur2 have to fetch exactly same records from the disk in every loop. Is there any way to get rid of this i/o time in every loop.
Any help will be greatly appreciated.
Thanks
Mahesh
|
|
|
|