Savepoint Funny
4 months ago
DECLARE
counter NUMBER(10):= 0;
CURSOR upd_tabs IS SELECT x, y FROM D;
BEGIN
FOR rec IN upd_tabs LOOP
UPDATE T1 SET y= rec.y WHERE x=rec.x;
UPDATE T2 SET y= rec.y WHERE z=rec.x;
UPDATE T3 SET y= rec.y WHERE z=rec.x;
counter:=counter+1;
IF MOD(counter,1000) = 0 )THEN
INSERT INTO update_log (COMMIT_TIME, COMMIT_COUNTER)
VALUES (SYSDATE, counter);
COMMIT;
END IF;
IF upd_tabs%NOTFOUND THEN
EXIT;
END IF;
END LOOP;
COMMIT;
END;
update
( select
T1.y T1_y,
D.y D_y
from
T1,
D
where T1.x = D.x
)
set
T1_y = D_y
/
denis@DB10G>
denis@DB10G> create table t1
2 ( x int constraint t1_pk primary key,
3 y int );
Table created.
denis@DB10G>
denis@DB10G> create table t2
2 ( x int constraint t2_pk primary key,
3 y int );
Table created.
denis@DB10G>
denis@DB10G> insert into t1 values (1,1);
1 row created.
denis@DB10G> insert into t1 values (2,1);
1 row created.
denis@DB10G> insert into t1 values (3,1);
1 row created.
denis@DB10G>
denis@DB10G> insert into t2 values (2,2);
1 row created.
denis@DB10G> insert into t2 values (3,2);
1 row created.
denis@DB10G>
denis@DB10G>
denis@DB10G> -- update a join
denis@DB10G> set autotrace on explain
denis@DB10G> update
2 ( select /*+ USE_NL(t1) INDEX (t1 t1_pk ) */
3 t1.y t1_y, t2.y t2_y
4 from t1, t2
5 where t1.x = t2.x )
6 set t1_y = t2_y
7 /
2 rows updated.
Execution Plan
----------------------------------------------------------
Plan hash value: 2040170521
---------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------------------------
0 UPDATE STATEMENT 2 104 5 (0) 00:00:01
1 UPDATE T1
2 NESTED LOOPS 2 104 5 (0) 00:00:01
3 TABLE ACCESS FULL T2 2 52 3 (0) 00:00:01
4 TABLE ACCESS BY INDEX ROWID T1 1 26 1 (0) 00:00:01
* 5 INDEX UNIQUE SCAN T1_PK 1 0 (0) 00:00:01
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."X"="T2"."X")
Note
-----
- dynamic sampling used for this statement
denis@DB10G>
denis@DB10G> set autotrace off;
denis@DB10G>
denis@DB10G>> select * from t1;
X Y
---------- ----------
1 1
2 2
3 2
denis@DB10G>
denis@DB10G> -- for larger tables the hint may not be necessary, Oracle can
denis@DB10G> -- pick up the right plan
denis@DB10G>
denis@DB10G> insert into t1 select rownum + 5 , 1
2 from all_objects
3 /
49841 rows created.
denis@DB10G>
denis@DB10G> analyze table t1 compute statistics;
Table analyzed.
denis@DB10G>
denis@DB10G> analyze table t2 compute statistics;
Table analyzed.
denis@DB10G>
denis@DB10G> set autotrace on explain
denis@DB10G> update
2 ( select t1.y t1_y, t2.y t2_y
3 from t1, t2
4 where t1.x = t2.x )
5 set t1_y = t2_y
6 /
2 rows updated.
Execution Plan
----------------------------------------------------------
Plan hash value: 2040170521
---------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------------------------
0 UPDATE STATEMENT 2 20 5 (0) 00:00:01
1 UPDATE T1
2 NESTED LOOPS 2 20 5 (0) 00:00:01
3 TABLE ACCESS FULL T2 2 8 3 (0) 00:00:01
4 TABLE ACCESS BY INDEX ROWID T1 1 6 1 (0) 00:00:01
* 5 INDEX UNIQUE SCAN T1_PK 1 0 (0) 00:00:01
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."X"="T2"."X")
denis@DB10G> set autotrace off
denis@DB10G>