Oracle的更新操作优化(2)

SQL> UPDATE T SET TYPE = 
2 ( 
3 SELECT TYPE 
4 FROM 
5 ( 
6 SELECT T.ID, DECODE(T1.ID, NULL, 0, 1) TYPE 
7 FROM T, 
8 ( 
9 SELECT T1.ID 
10 FROM T1@YTK102 T1, T2@YTK102 T2, T3@YTK102 T3 
11 WHERE T1.ID = T2.ID 
12 AND T2.ID = T3.ID 
13 ) T1 
14 WHERE T.ID = T1.ID(+) 
15 AND T.TYPE != DECODE(T1.ID, NULL, 0, 1) 
16 ) A 
17 WHERE T.ID = A.ID 
18 ) 
19 WHERE EXISTS 
20 ( 
21 SELECT 1 
22 FROM 
23 ( 
24 SELECT T.ID, DECODE(T1.ID, NULL, 0, 1) TYPE 
25 FROM T, 
26 ( 
27 SELECT T1.ID 
28 FROM T1@YTK102 T1, T2@YTK102 T2, T3@YTK102 T3 
29 WHERE T1.ID = T2.ID 
30 AND T2.ID = T3.ID 
31 ) T1 
32 WHERE T.ID = T1.ID(+) 
33 AND T.TYPE != DECODE(T1.ID, NULL, 0, 1) 
34 ) A 
35 WHERE T.ID = A.ID 
36 ) 
37 ; 

SQL> DECLARE 
2 V_TYPE NUMBER; 
3 BEGIN 
4 FOR I IN (SELECT ID, TYPE FROM T) LOOP 
5 SELECT DECODE(COUNT(T1.ID), 0, 0, 1) INTO V_TYPE 
6 FROM T1@YTK102 T1, T2@YTK102 T2, T3@YTK102 T3 
7 WHERE T1.ID = T2.ID 
8 AND T2.ID = T3.ID 
9 AND T1.ID = I.ID; 
10 
11 IF I.TYPE != V_TYPE THEN 
12 UPDATE T SET TYPE = V_TYPE WHERE ID = I.ID; 
13 END IF; 
14 END LOOP; 
15 END; 
16 /