Home » SQL & PL/SQL » SQL & PL/SQL » Need advice on optimizing Update statement
Need advice on optimizing Update statement [message #272290] Thu, 04 October 2007 08:05 Go to next message
hellcat
Messages: 84
Registered: January 2006
Location: Zug, Switzerland
Member
Hi

I've got following situation:
create table a (c1 varchar2(4), c2 varchar2(4), c3 varchar2(4));
create table b (c1 varchar2(4), c2 varchar2(4), c3 varchar2(4));

insert into a values ('a','b','c');
insert into a values ('1','2','3');
insert into a values ('x','y','z');
insert into a values ('e','f','g');
insert into b values ('a','bb','cc');
insert into b values ('e','ff','gg');

and I need to update a.c2, a.c3 to the values of b.c2, b.c3 of the corresponding records. like:
update a
set (c2, c3) = (
  select b.c2, b.c3
  from b
  where b.c1 = a.c1
)
where a.c1 in (
  select b.c1
  from b
);


Now I have to do this for a two very large tables and I thought you guys might have better performing solution - update statement wise? Or is this the way to do it? Can we re-write the statement in a way to read table B only once?

(Actually the performance is acceptable - but I'm always trying to do the best solution possible)

Many thanks.
/hc

[Updated on: Thu, 04 October 2007 08:07]

Report message to a moderator

Re: Need advice on optimizing Update statement [message #272292 is a reply to message #272290] Thu, 04 October 2007 08:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you can guarantee that there is only one record in table b that matches any given record in table a, you can use an UPDATE VIEW:
alter table b add (constraint b_unq unique (c1));

update (select a.c2 ,a.c3 ,b.c2 new_c2 , b.c3 new_c3
        from a, b
        where b.c1 = a.c1)
set c2 = new_c2
   ,c3 = new_c3;


Re: Need advice on optimizing Update statement [message #272293 is a reply to message #272292] Thu, 04 October 2007 08:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Oh - thanks for providing create & insert scripts. That's a politeness that is becoming increasingly rare lately.
Re: Need advice on optimizing Update statement [message #272295 is a reply to message #272292] Thu, 04 October 2007 08:24 Go to previous messageGo to next message
hellcat
Messages: 84
Registered: January 2006
Location: Zug, Switzerland
Member
Mh, I already tried that before (read about that in the documentation) but I got ORA-01779 because I haven't had a unique constraint on b.c1 - seems like I gave up too early.
With the constraint it works fine.

Thanks a lot for your help!

/hc
Re: Need advice on optimizing Update statement [message #272297 is a reply to message #272290] Thu, 04 October 2007 08:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL> create table a (c1 varchar2(4), c2 varchar2(4), c3 varchar2(4));
SQL> create table b (c1 varchar2(4), c2 varchar2(4), c3 varchar2(4));
SQL> insert into a values ('a','b','c');
SQL> insert into a values ('1','2','3');
SQL> insert into a values ('x','y','z');
SQL> insert into a values ('e','f','g');
SQL> insert into b values ('a','bb','cc');
SQL> insert into b values ('e','ff','gg');
SQL> commit;
SQL> select * from a order by c1;
C1   C2   C3
---- ---- ----
1    2    3
a    b    c
e    f    g
x    y    z

4 rows selected.

SQL> select * from b order by c2;
C1   C2   C3
---- ---- ----
a    bb   cc
e    ff   gg

2 rows selected.

SQL> update a
  2  set (c2, c3) = (
  3    select b.c2, b.c3
  4    from b
  5    where b.c1 = a.c1
  6  )
  7  where a.c1 in (
  8    select b.c1
  9    from b
 10  );

2 rows updated.

SQL> select * from a order by c1;
C1   C2   C3
---- ---- ----
1    2    3
a    bb   cc
e    ff   gg
x    y    z

4 rows selected.

SQL> rollback;
SQL> merge into a using b on (b.c1 = a.c1)
  2  when matched then update set a.c2 = b.c2, a.c3 = b.c3
  3  /

2 rows merged.

SQL> select * from a order by c1;
C1   C2   C3
---- ---- ----
1    2    3
a    bb   cc
e    ff   gg
x    y    z

4 rows selected.

SQL> rollback;
SQL> alter table a add primary key (c1);
SQL> alter table b add primary key (c1);
SQL> update (select a.c1, a.c2 ac2, a.c3 ac3, b.c1, b.c2 bc2, b.c3 bc3 from a, b where b.c1=a.c1)
  2  set ac2 = bc2, ac3 = bc3
  3  /

2 rows updated.

SQL> select * from a order by c1;
C1   C2   C3
---- ---- ----
1    2    3
a    bb   cc
e    ff   gg
x    y    z

4 rows selected.

The second statement is only valid is you have keys.

Regards
Michel
Re: Need advice on optimizing Update statement [message #272304 is a reply to message #272297] Thu, 04 October 2007 08:36 Go to previous messageGo to next message
hellcat
Messages: 84
Registered: January 2006
Location: Zug, Switzerland
Member
Ah, the MERGE solution looks great as well.
thank you!
I think I got what I was looking for.

/hc
Re: Need advice on optimizing Update statement [message #272305 is a reply to message #272304] Thu, 04 October 2007 08:42 Go to previous messageGo to next message
hellcat
Messages: 84
Registered: January 2006
Location: Zug, Switzerland
Member
Note: the merge only works in 10g, not in 9i.
WHEN NOT MATCHED THEN clause is mandatory in 9i.
Re: Need advice on optimizing Update statement [message #272306 is a reply to message #272304] Thu, 04 October 2007 08:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Functionally, the Merge is the same as the update view - both of them require that there be at most one row in B for a given row in A.

The merge is probably more flexible and easy to understand.
Re: Need advice on optimizing Update statement [message #272312 is a reply to message #272305] Thu, 04 October 2007 09:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
@hellcat,
yes, WHEN NOT MATCHED is mandatory in 9i but you can add a dummy clause there as it is never reached:
WHEN NOT MATCHED insert (c1) values (null)

@JRowbottom,
Merge does not have the same constraints as updatable view as I show it there (there is no key on my tables).

Regards
Michel
Re: Need advice on optimizing Update statement [message #272467 is a reply to message #272312] Fri, 05 October 2007 03:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
MERGE doesn't require there to be constraints guaranteeing unique row in B for every A, but if there are multiple rows in B with different values, then it does object quite strenuously:
SQL> create table a (c1 varchar2(4), c2 varchar2(4), c3 varchar2(4));
Table created.

SQL> create table b (c1 varchar2(4), c2 varchar2(4), c3 varchar2(4));
Table created.

SQL> insert into a values ('a','b','c');
SQL> insert into a values ('1','2','3');
SQL> insert into a values ('x','y','z');
SQL> insert into a values ('e','f','g');
SQL> insert into b values ('a','bb','cc');
SQL> insert into b values ('e','ff','gg');
SQL> commit;

Commit complete.

SQL> merge into a using b on (b.c1 = a.c1)
  2  when matched then update set a.c2 = b.c2, a.c3 = b.c3;

2 rows merged.

SQL> select * from a;

C1   C2   C3
---- ---- ----
a    bb   cc
1    2    3
x    y    z
e    ff   gg

SQL> rollback;
Rollback complete.

SQL> insert into b values ('a','dd','ee');
1 row created.

SQL> merge into a using b on (b.c1 = a.c1)
  2  when matched then update set a.c2 = b.c2, a.c3 = b.c3;
merge into a using b on (b.c1 = a.c1)
           *
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables


So, while you can get away without having the constraints to guarantee it, you still need to have at most 1 value in B for each value in A
Re: Need advice on optimizing Update statement [message #272471 is a reply to message #272467] Fri, 05 October 2007 03:19 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes, you're right what I meant is you don't need to have a real/physical constraint valid for all and any data.
You only need the constraint to be valid for the set of rows on which your query works.

Regards
Michel

Previous Topic: column as a collection
Next Topic: Help Needs for Storing of BLOB
Goto Forum:
  


Current Time: Fri Feb 14 09:55:43 CST 2025