Re: how to boost a slow update query

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" <Olaf.Musch_at_computer.org> wrote in message news:<c75q4k$1bjp$1_at_ID-168133.news.uni-berlin.de>...
> <vuht2000_at_yahoo.com> schrieb im Newsbeitrag news:c9cd729c.0405030721.220fe570_at_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
Received on Wed May 05 2004 - 13:59:24 CDT