| updating a huge database [message #35980] |
Sun, 28 October 2001 17:46  |
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 primery key as the previous one (16000). Now, i want to update the older table with newer data in the new table. I wrote a pl/sql procedure to achieve this task, but first, it takes too long ( more than 3 hours) , secondly the update task is not achieved, i mena there were no updated data.
can you have a look at the below procedure and tell me what is wrong with it and where i am making mistake?
thank alot for your help in advance.
cheers :)
my Pl/sql procedure :
********************
declare
cursor c1 is
select totid,unit from test where unit is not null;
--totid is the primary key of the table
--test is the newer table
tot csource.TOTID%type;--tot var will hold the totid values
uni csource.unit%type;--uni var will hold the column data that will be updated
begin
open c1;
loop
fetch c1 into tot,uni;
exit when c1%notfound;
update csource set unit=uni where trim(totid)=tot;
--csource is the older table
end loop;
commit;
close c1;
end;
----------------------------------------------------------------------
|
|
|
|
| Re: updating a huge database [message #35992 is a reply to message #35980] |
Mon, 29 October 2001 07:21  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
You should do this in just a straight update statement - no need for PL/SQL here:
update csource cs
set unit =
(select unit from test t
where t.totid = trim(cs.totid)
and t.unit is not null)
where exists
(select 1 from test t
where t.totid = trim(cs.totid)
and unit is not null);
You will definitely want an index on totid in test (if this column is the primary key, than the index is already in place).
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
