Path: newssvr20.news.prodigy.com!newsmst01a.news.prodigy.com!prodigy.com!news.glorb.com!postnews1.google.com!not-for-mail From: vuht2000@yahoo.com Newsgroups: comp.databases.oracle.misc Subject: Re: how to boost a slow update query Date: 5 May 2004 11:59:24 -0700 Organization: http://groups.google.com Lines: 43 Message-ID: References: <9ue590teg6n2c7u98ag757roaeq4rusqu9@4ax.com> NNTP-Posting-Host: 128.175.253.215 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1083783565 29846 127.0.0.1 (5 May 2004 18:59:25 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Wed, 5 May 2004 18:59:25 +0000 (UTC) Xref: newssvr20.news.prodigy.com comp.databases.oracle.misc:138065 Hi Olaf, yes it helps. But how if I remove the where clause, i.e., how would you do with this query: update table1 a set a.val1 = (select val1_new from table2 b where a.id=b.id) it runs overnight to finish. I could create a view joining the 2 tables and then update on the view, and it is pretty fast. But I don't always have permission to create views, and, why do I need to go that way while I have update as a handy tool for this purpose? I started to think that oracle has problem implementing update query. Can you guys please correct me? Tam "Olaf Musch" wrote in message news:... > schrieb im Newsbeitrag news:c9cd729c.0405030721.220fe570@posting.google.com... > > Thanks Sybrand, > > there's a type, it should be "...where a.val2=a.val3" > > the plan shows that both tables have full table scan. So which columns > > should I place index on and/or how to force oracle to use them to > > avoid fts? > > regards, > > Tam > > Hi Tam, > > without checking it, I first think you should > create indeces on the columns you use in the > update restriction: val2 and val 3 of table1. > > This is because in this restriction, the rdbms > has to read and compare the values for EVERY > row of table1, so that's the reason for the > fts on it. > With the correction that Sybrand already > told you and these indeces, I presume that the > fts on table2 will no longer appear in > your explain plan. > > hth > > Olaf