bypass_ujvc（Updatable Join View Check）

출처: http://sarangzigi.com/284

update T1
set c1 = ( select c2 from T2 where T2.key = T1.key )
where exists ( select c2 from T2 where T2.key = T1.key )

위와 같은 쿼리는 풀려고 하면 NL로 시간 죽이기 딱 좋은 쿼리. 오라클 쪽에서 권장한 방법은 T2가 작고 잘 Analyzed 되어있을 경우 아래와 같다.

scott@ORA734.WORLD> create table t1
2 ( x int constraint t1_pk primary key,
3 y int );

scott@ORA734.WORLD> create table t2
2 ( x int constraint t2_pk primary key,
3 y int );

scott@ORA734.WORLD> insert into t1 values ( 1, 1 );
scott@ORA734.WORLD> insert into t1 values ( 2, 1 );
scott@ORA734.WORLD> insert into t1 values ( 3, 1 );

scott@ORA734.WORLD> insert into t2 values ( 2, 2 );
scott@ORA734.WORLD> insert into t2 values ( 3, 2 );

scott@ORA734.WORLD> set autotrace on explain
scott@ORA734.WORLD> 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 /

Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE (Cost=83
1 0 NESTED LOOPS (Cost=83 Card=68 Bytes=3536)
2 1 TABLE ACCESS (FULL) OF 'T2'
3 1 INDEX (UNIQUE SCAN) OF 'T1_PK' (UNIQUE)

scott@ORA734.WORLD> set autotrace off
scott@ORA734.WORLD> select * from t1
2 /

X Y
---------- ----------
1 1
2 2
3 2

I used hints to help the optimizer along -- they may not be necessary if
you've alot of rows and have analyzed the tables.

The USE_NL hint says to "nested loops join to T1", and the index hint helps it
pick the correct index for doing the join.

To see that the hints might not be necessary:


scott@ORA734.WORLD> insert into t1 select rownum+5, 1
2 from all_objects
3 /

1128 rows created.

scott@ORA734.WORLD> analyze table t1 compute statistics
2 /

Table analyzed.

scott@ORA734.WORLD> analyze table t2 compute statistics
2 /

Table analyzed.

scott@ORA734.WORLD> set autotrace on explain
scott@ORA734.WORLD> 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
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS (Cost=3 Card=2 Bytes=38)
2 1 TABLE ACCESS (FULL) OF 'T2'
3 1 INDEX (UNIQUE SCAN) OF 'T1_PK' (UNIQUE)

scott@ORA734.WORLD> set autotrace off

이때 시도해 볼 수 있는 bypass-ujvc 문서화 되어있지 않은 Hint

UPDATE /*+ BYPASS_UJVC */ (
SELECT A.C1, A.C2, A.C3 C3A, B.C3 C3B
FROM T1 A, T2 B
WHERE A.key = B.key )
SET
A.C1 = B.C1;