| what is the fastest way to update a big table (50000rows*20columns) [message #36033] |
Wed, 31 October 2001 01:55  |
yilmaz
Messages: 4
Registered: September 2001
|
Junior Member |
|
|
hi guys,
i have a big problem in updating an oracle 8i 1.7 database table with more than 50000 rows and 20 columns. The problem is: i have another table with approximately 17000 rows and 12 columns. The latter one holds newer data, and most of them have the same primary key as the previous one (16000). Now, i want to update the older table with newer data from the new table. I wrote a pl/sql procedure to achieve this task, but it takes too long ( nearly 12 hours) ,
can you have a look at the below procedure and tell me what is wrong with it and where i am making mistake? CAn you suggest me a faster and more efficient way to do that?
thanks alot for your help in advance.
cheers :)
my Pl/sql procedure :
********************
declare
cursor c1 is
select c.totid,j.source from join j,cs c where j.source is not null
and trim(j.totid)=trim(c.totid);
--join is the new table (17700 rows)
--cs id the older one (50300rows)
tot cs.TOTID%type;--totid is the primary key for both tables
src cs.source%type;--source is the column to be updated
begin
open c1;
loop
fetch c1 into tot,src;
tot:=trim(tot);
exit when c1%notfound;
update cs set source=src where trim(totid)=tot;
end loop;
commit;
close c1;
end;
----------------------------------------------------------------------
|
|
|
|
| Re: what is the fastest way to update a big table (50000rows*20columns) [message #36038 is a reply to message #36033] |
Wed, 31 October 2001 06:55  |
Suresh Vemulapalli
Messages: 624
Registered: August 2000
|
Senior Member |
|
|
you dont need to use TRIM when column is varchar2 datatype.try below code. it should take less than 2,3 minutes. Oracle dont use index when you use functions like trim,substr etc.
declare
cursor c1 is
select c.totid,j.source from join j,cs c where j.source is not null
and j.totid=c.totid;
--join is the new table (17700 rows)
--cs id the older one (50300rows)
tot cs.TOTID%type;--totid is the primary key for both tables
src cs.source%type;--source is the column to be updated
begin
open c1;
loop
fetch c1 into tot,src;
tot:=trim(tot);
exit when c1%notfound;
update cs set source=src where totid=tot;
end loop;
commit;
close c1;
end;
----------------------------------------------------------------------
|
|
|
|
| Re: what is the fastest way to update a big table (50000rows*20columns) [message #36081 is a reply to message #36033] |
Fri, 02 November 2001 19:57 |
Suresh Baral
Messages: 11
Registered: October 2001
|
Junior Member |
|
|
Hi Yilmaz,
I am agreed with Todd Barry and Suresh Vemulapalli.
Best is to use Todd Barry's SQl and don't use trim syntax which is mentioned by Suresh Vwmurapalli. The trim will eliminate to use any index key which might be defined on that particular column.
Before running that query, you may run for explain plan to see the way of SQL is running.
Regards
Suresh
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
