Multi column update
Sashi
can't get it right.
Here's the intent.
update A set (a1,a2,a3) = (select sum(b1),sum(b2),sum(b3) from B group
by b4 where B.b4 = A.A4)
I got two tables, A and B. The join parameters are A.A4 and B.B4.
I need to updated a1, a2 and a3 in A to equal the sums of b1, b2 and
b3 in B. The sum needs to be grouped by b4 and A.a4 needs to equal
B.b4.
Can someone help?
TIA,
Sashi
Charles Hooper
It appears that you have the GROUP BY and WHERE clause in the wrong
order - at least that is the problem that I see. An example:
SELECT
1
FROM
DUAL;
1
----------
1
SELECT
1
FROM
DUAL
GROUP BY
1;
1
----------
1
SELECT
1
FROM
DUAL
WHERE
1=1;
1
----------
1
SELECT
1
FROM
DUAL
GROUP BY
1
WHERE
1=1;
1
----------
1
SQL> SELECT
2 1
3 FROM
4 DUAL
5 GROUP BY
6 1
7 WHERE
8 1=1;
WHERE
*
ERROR at line 7:
ORA-00933: SQL command not properly ended
(Note that the above is the syntax order you are attempting to use)
SELECT
1
FROM
DUAL
WHERE
1=1
GROUP BY
1;
1
----------
1
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Sashi
I should've been more careful with my post, I guess.
select sum(b1), sum(b2), sum(b3)
from B
group by b4
Would yield a table.
Now I want to join that temp table to my my table 'A' and update three
columns in A, which joining a4 with b4.
Example
Table A:
------------------------------------------
a1 , a2 , a3 , a4
------------------------------------------
null, null, null, 'Candy'.
null, null, null, 'Cookies'
Tabl3 B;
------------------------------------------
b1 , b2 , b3 , b4
------------------------------------------
12, 22, .25, 'Candy'
10, 12, .50, 'Candy'
22, 12, .35, 'Cookies'
20, 18, .40, 'Cookies
'
So, sum up b1, b2, b3, grouping by 'b4'.
Plug these back into a4 to get
------------------------------------------
a1 , a2 , a3 , a4
------------------------------------------
22, 34, .75, 'Candy'
42, 30, .75, 'Cookies'.
Hope this is a good example.
TIA,
Sashi
Charles Hooper
Sashi,
I think that you missed the message in my example - you need to
specify the WHERE keyword in the SQL statement before the GROUP BY
keyword. Here is an example which more closely matches your setup:
CREATE TABLE T3 (
A1 NUMBER,
A2 NUMBER,
A3 NUMBER,
A4 VARCHAR2(10));
CREATE TABLE T4 (
B1 NUMBER,
B2 NUMBER,
B3 NUMBER,
B4 VARCHAR2(10));
INSERT INTO T3 VALUES(null,null,null,'Candy');
INSERT INTO T3 VALUES(null,null,null,'Cookies');
INSERT INTO T4 VALUES(12, 22, .25, 'Candy' );
INSERT INTO T4 VALUES(10, 12, .50, 'Candy' );
INSERT INTO T4 VALUES(22, 12, .35, 'Cookies' );
INSERT INTO T4 VALUES(20, 18, .40, 'Cookies' );
COMMIT;
SELECT
*
FROM
T3 A;
A1 A2 A3 A4
---------- ---------- ---------- -------
Candy
Cookies
In the above, note that T3 is your table A, and T4 is your table B.
Now if I try to use the SQL statement you provided, I receive an
error:
update T3 A set (a1,a2,a3) = (select sum(b1),sum(b2),sum(b3) from T4 B
group by b4 where B.b4 = A.A4);
ERROR at line 2:
ORA-00907: missing right parenthesis
If I try again, this time placing the WHERE clause before the GROUP BY
clause:
update T3 A set (a1,a2,a3) = (select sum(b1),sum(b2),sum(b3) from T4 B
where B.b4 = A.A4 group by b4);
2 rows updated.
The SQL statement worked.
SELECT
*
FROM
T3 A;
A1 A2 A3 A4
---------- ---------- ---------- -------
22 34 .75 Candy
42 30 .75 Cookies
Sashi
Charles,
> On Nov 9, 3:03 pm, Sashi <small...@gmail.com> wrote:
> > Hi all, I'm trying to write where appears to be a simple query but
> > can't get it right.
> > Here's the intent.
> > update A set (a1,a2,a3) = (select sum(b1),sum(b2),sum(b3) from B group
> > by b4 where B.b4 = A.A4)
> > I got two tables, A and B. The join parameters are A.A4 and B.B4.
> > Can someone help?
> > TIA,
> > Sashi
> 1
> ----------
> 1
> 1
> ----------
> 1
> 1
> ----------
> 1
> 1
> ----------
> 1
> 1
> ----------
> 1
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.
Charles,
I should've been more careful with my post, I guess.
It works like a charm.
Thanks for your time.
Sashi
Sashi
The sub-query runs in about 90 seconds and comes up with a result set
with 132 records. In my example, this would be the B table. The A
table also has the same number of records.
However, the update runs for about 22 minutes.
Since the subquery runs rather fast (and it has to go thru about 9
million rows to come up with this summary), I'm surprised that the
update should take so long.
The example I've given is a sort of simplified version of my query,
but it's a very good approximation.
i.e., select sum(b1), sum(b2), sum(b3) from B group by b4 runs in less
than 90 seconds.
So it's safe to assume that the join condition is causing the
inefficiency.
Any ideas on how to make it run quicker?
Thanks,
Sashi
Robert Klemme
> The sub-query runs in about 90 seconds and comes up with a result set
> with 132 records. In my example, this would be the B table. The A
> table also has the same number of records.
> However, the update runs for about 22 minutes.
> Since the subquery runs rather fast (and it has to go thru about 9
> million rows to come up with this summary), I'm surprised that the
> update should take so long.
> The example I've given is a sort of simplified version of my query,
> but it's a very good approximation.
> i.e., select sum(b1), sum(b2), sum(b3) from B group by b4 runs in less
> than 90 seconds.
> So it's safe to assume that the join condition is causing the
> inefficiency.
> Any ideas on how to make it run quicker?
You could look at the execution plan, e.g. with proper permissions set
in SQL Plus SET AUTOTRACE ON or SET AUTOTRACE TRACEONLY.
Kind regards
robert
--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/
Charles Hooper
Sashi,
I must have missed your follow up post. Robert offers a good
suggestion to determine why the update is slow.
If I had to make a guess, I would guess one of three things:
* The update is taking 3 columns of table A with 9 million rows from a
NULL value to a non-null value. This will increase the space
requirements for each row, and could cause performance problems as
Oracle tries to find room for the expanded rows. Based on what has
been report in various forums (and my own tests) this appears to be
more of a problem in ASSM tablespaces, especially when a block size
larger than 8KB is used. With Robert's suggestion you would see a
large number of current mode block gets. Jonathan Lewis has written a
bit about finding a related bug in ASSM tablespaces which use a block
size larger than 8KB when column values are updated, which causes the
row to expand, and Oracle to visit many blocks in an attempt to find
one with sufficient free space.
* The UPDATE statement is generating a lot of redo, with the speed
limited by how fast the server is able to write the redo information.
This may be aggravated by the need to relocate the rows due to the
first problem mentioned.
* The query is performing the GROUP BY operation on table B for each
row in table A. You might consider defining a temp table that clears
its contents ON COMMIT, insert the rows from the subquery into the
temp table, and then reference that temp table with your update query:
INSERT INTO
TEMP_B
SELECT
B4,
SUM(B1) B1,
SUM(B2) B2,
SUM(B3) B3
FROM
T4 B
GROUP BY
B4;
UPDATE
T3 A
SET
(A1,A2,A3) =
(SELECT
B1,
B2,
B3
FROM
TEMP_B B
WHERE
B.B4 = A.A4);
A 10046 trace at level 8 might provide enough information to determine
what is happening - make certain that you execute a simple SQL
statement, such as SELECT SYSDATE FROM DUAL; after the update
statement to increase the chances that the row source execution plan
will be written to the trace file. Take a look at the TKPROF
interpreted version of the 10046 trace file to help determine what it
takes so long for the update.
Sashi
Hi Charles, I had a real duh moment when I saw your post.
Of course! Just use a temp table to store the intermediate values and
avoid the (9x10^6 * 132 ) comparisons!!
Thanks a lot for the pointer. I'm using a temp table and the update
runs in less than a second.
Talk about efficiency!!
Regards,
Sashi