From yilmaz@hwajet.com.tw Wed, 31 Oct 2001 07:35:34 -0800 From: "yilmaz" Date: Wed, 31 Oct 2001 07:35:34 -0800 Subject: Re: what is the fastest way to update a big table with more than 50000 rows and 20 columns Message-ID: MIME-Version: 1.0 Content-Type: text/plain if i don't use trim, my program can't compare those two totids since one of them is char 255 and the other varchar2 255. In both case without using trim it can't find any totid values (e.g : i am testin with the folowing sql: select * from join where totid='00037201'; and i am gettitng (no values matched !) result. now i am working on changing those char255 and varchar2 (255) values to char(8), but still there are a lot of headaches can you help me with this ? cheers :) ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" Sent: Wednesday, October 31, 2001 9:10 PM 50000 rows and 20 columns > Try this: > > update cs c1 set c1.source = ( > select j.source > from join j, cs c > where trim(j.totid)=trim(c.totid) > and j.source is not null) > where trim(c1.totid)=trim(c.totid); > > Should be faster, at least because you don't have to switch between PL/SQL > and SQL. > Also, do you really have to use trim()? > I guess, 'TITID' is char type. Why not make it varchar2? In this case, you > will not have to use trim(). Using function prevents from using indexes on > both tables, so most probably you are doing table scans. > > Igor Neyman, OCP DBA > ineyman@perceptron.com > > > ----- Original Message ----- > To: "Multiple recipients of list ORACLE-L" > Sent: Wednesday, October 31, 2001 7:15 AM > rows and 20 columns > > > > 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; > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: yilmaz > > INET: yilmaz@hwajet.com.tw > > > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California -- Public Internet access / Mailing Lists > > -------------------------------------------------------------------- > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Igor Neyman > INET: ineyman@perceptron.com > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yilmaz INET: yilmaz@hwajet.com.tw Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).