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  |
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   |
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 #272295 is a reply to message #272292] |
Thu, 04 October 2007 08:24   |
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   |
 |
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 #272467 is a reply to message #272312] |
Fri, 05 October 2007 03:03   |
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  |
 |
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
|
|
|
Goto Forum:
Current Time: Fri Feb 14 09:55:43 CST 2025
|