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

and i forgot:
the first select for the cursor doesn't use any index either fpr the same reason as stated above

Stephan

> Hallo,
>
> your update definitely doesn't use the primary key or any other index
> (unless you have defined an function index on trim(totid) on cs).
>
> Try someting like
> ...
> update cs set source=src where totid like tot || '%';
> ...
> or so
>
> btw, why do you trim the primary key's? aren't they equal in both tables
> or not. If they are equal, compare without any truncation.
>
> hth
> Stephan
>
> yilmaz schrieb:
>
> > 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;
>
Received on Wed Oct 31 2001 - 13:43:30 CET