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;

For faster processing I would avoid using cursors if the coding can be done by plain SQL. The code below accomplishes the same thing what your code does.

begin

   UPDATE cs c
   SET source=(SELECT j.source

                       FROM JOIN j
                      WHERE j.totid = c.totid)

Since I do not have sample data here, please try running the above simplified code and please report to this NG its performance. I believe if your tables (cs and join) are properly indexed (or having primary keys based on totid), the simplified code will execute faster than the original code.

Hope this helps.

Roel Received on Thu Nov 01 2001 - 04:23:26 CET