| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: help: update statement fill up rollback segment
A copy of this was sent to ewong74_at_netscape.net
(if that email address didn't require changing)
On Wed, 24 Feb 1999 23:10:05 GMT, you wrote:
>Hi there,
>
>I have the following update statement:
>
>UPDATE table_name t1 SET a1 =
>(SELECT b1 FROM other_table t2
> WHERE t1.a2 = t2.b2
> AND t1.a1 <> t2.b1);
>
>The table has 10M rows and (t1.a1 <> t2.b1) is 90%+ true in the table. So it
>just fill up the rollback segment without finishing it. I thought about
>increasing the rollback segment but I don't think it's worth to create that
>huge space just for rollback. I would like to know is there a way to make
>the update commit in a certain interval or just simply make it doen't use
>rollback?
>
did you know you are updating 10M rows? (not 90% of 10M)
consider:
SQL> create table t1 ( a1 int, a2 int primary key ); Table created.
SQL> create table t2 ( b1 int, b2 int primary key ); Table created.
SQL> insert into t1 values ( 0, 1 );
SQL> insert into t1 values ( 0, 2 );
SQL> insert into t2 values ( 1, -10 ); SQL> insert into t2 values ( -1, -20 ); SQL> insert into t2 values ( 5, 1 );
SQL> commit;
SQL> select * from t1;
A1 A2
---------- ----------
0 1
0 2
SQL>
SQL> UPDATE t1 SET a1 =
2 (SELECT b1 FROM t2 WHERE t1.a2 = t2.b2 AND t1.a1 <> t2.b1);
2 rows updated.
SQL>
SQL> select * from t1;
A1 A2
---------- ----------
5 1
2
so, since there is no where clause, every row is updated and when the subquery returns a NULL set, a1 is set to null.....
ways to recode to update the 10% of 10M rows:
SQL> rollback;
SQL> select * from t1;
A1 A2
---------- ----------
0 1
0 2
SQL> update
2 ( select * from t1, t2 where t1.a2 = t2.b2 and t1.a1 <> t2.b1 )
3 set a1 = b1
4 /
1 row updated.
SQL> select * from t1;
A1 A2
---------- ----------
5 1
0 2
that counts on the keys being in place and is the fastest query that will do the update. another solution that works without keys is:
SQL> rollback;
Rollback complete.
SQL>
SQL> select * from t1;
A1 A2
---------- ----------
0 1
0 2
SQL> UPDATE t1 SET a1 =
2 (SELECT b1 FROM t2 WHERE t1.a2 = t2.b2 AND t1.a1 <> t2.b1)
3 where exists
4 (SELECT b1 FROM t2 WHERE t1.a2 = t2.b2 AND t1.a1 <> t2.b1);
1 row updated.
SQL> select * from t1;
A1 A2
---------- ----------
5 1
0 2
>Thanx in advance.
>
>Ed
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |