Re: (SQL) UPDATING a table from a join

[Quoted] The problem is that the where clauses in both the outer 'update' statement and the inner 'select' statement aren't utilizing the primary keys for either table. You're therefore doing a full_table scan on both tables for every record.

Try:

 UPDATE PIECE
 SET LEVEL='02'
 WHERE
 PIECE_NUMBER IN (SELECT PIECE_NO FROM PIECE_LIST WHERE  LISTNAME='TOTO' AND ...)
 AND LEVEL='04'
 AND ...; That should utilize the primary keys correctly on both tables and be as fast as possible. An additional index that concatenates piece_number||level on PIECE will improve performance even more.

-Brendan

In article <357FBEBA.1FB3_at_hospvd.ch>,   ebaechle_at_hospvd.ch wrote:
>
> Hi,
>
> Is there a way to perform an EFFICIENT update, where the
> selection of rows to update depends on a join?
>
> I have two tables of the following style:
>
> CREATE OR REPLACE TABLE PIECE
> (....
> PIECE_NUMBER VARCHAR2(10),
> LEVEL VARCAHR2(2),
> ....
> )
>
> CREATE OR REPLACE TABLE PIECE_LIST
> (....
> LISTNAME VARCHAR2(10),
> PIECE_NO VARCHAR2(10),
> .....
> )
>
> The two real tables are part of a package and I am NOT
> allowed to alter them. The first one contains several
> hundreds of thousands elements. The second one may be
> of a similar size.
>
> I would like to update the field "LEVEL" to the value "02"
> for all rows such that the belong to the listname "TOTO" and
> such that their field "LEVEL" currently contain the value "04".
>
> Counting the number of relevant occurences is very easy and fast:
> SELECT COUNT (*) FROM PIECE, PIECE_LIST
> WHERE ....
> LEVEL='04' AND
> ....
> LISTNAME='TOTO' AND
> PIECE_NO, PIECE_NUMBER;
>
> But I cannot specify a join for the update. The only way I found
> for doing it is:
>
> UPDATE PIECE
> SET LEVEL='02'
> WHERE
> ...
> LEVEL='04' AND
> PIECE_NUMBER IN (SELECT PIECE_NO FROM PIECE_LIST WHERE .... AND
> LISTNAME='TOTO');
>
> Needless to say, this request is abysmally slow! Althouhg the number of
> rows requiring update is under 200, it takes several hours to run. But
> I did not found a better way to do it. I tried to create a few uncluding
> the join between the two tables. the update was refused because the view
> was not "a key preserving table" or something like that.
>
> Does anyboday have an idea to make a faster UPDATE?
>
> Any help will be appreciated
>
> Best regards
>
> Emmanuel Baechler
> Lausanne
> Switzerland
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Thu Jun 11 1998 - 19:34:18 CEST