Re: what is the fastest way to update a big table (50000rows*20columns)

"yilmaz" <yilmazay_at_yahoo.com> wrote in message news:b8a3c655.0110310353.6aafca69_at_posting.google.com...
> hi guys,
> i have a big problem in updating an oracle 8i 1.7 database table with
> more than 50000 rows and 20 columns. Last time a friend helped me with
> this for some extend. But the problem is It takes toooo long to update
> a single column.Let me expleain my problem: 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;

update cs
set source =
(select src
 from join
 where join.totid = cs.totid)
where exists
(select 'x'
 from join
 where join.totid = cs.totid)

Just forget about the trim, if that primary key has been setup *correctly* you won't need it.
When you do need it, you have to know that both in this code as in your pl/sql procedure you will get full table scans on *both* join and cs

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address