Oracle多表关联更新

Oracle多表关联更新

更新原则:
1）更新的时候一定要加where条件，否则必然引起该字段的所有记录更新
2）跨表更新时，set和where时，尽量减少扫描次数，从而提高优化

一.最简单的形式-单表更新

--确认业务逻辑,筛选条件都在一个表内,进行单表更新
UPDATE CUSTOMERS SET CITY_NAME = '北京' WHERE CUSTOMER_ID < 1000;

二.两表(多表)关联update – set为简单的数据（直接是值），且仅在where字句中的连接

--这次提取的数据都是VIP，且包括新增的,所以顺便更新客户类别
UPDATE CUSTOMERS A -- 使用别名
   SET CUSTOMER_TYPE = '01' --01 为vip，00为普通
 WHERE EXISTS (SELECT 1 FROM TMP_CUST_CITY B WHERE B.CUSTOMER_ID = A.CUSTOMER_ID);

三.两表(多表)关联update – 被修改值由另一个表运算而来

UPDATE CUSTOMERS A -- 使用别名
   SET CITY_NAME =
        (SELECT B.CITY_NAME FROM TMP_CUST_CITY B WHERE B.CUSTOMER_ID = A.CUSTOMER_ID)
 WHERE EXISTS (SELECT 1 FROM TMP_CUST_CITY B WHERE B.CUSTOMER_ID = A.CUSTOMER_ID);

优化：单个字段的优化，简化为扫描一遍

UPDATE CUSTOMERS A -- 使用别名
   SET CITY_NAME = NVL((SELECT B.CITY_NAME
                          FROM TMP_CUST_CITY B
                         WHERE B.CUSTOMER_ID = A.CUSTOMER_ID),
                        A.CITY_NAME);

update 超过2个值（字段）

UPDATE CUSTOMERS A -- 使用别名
   SET (CITY_NAME, CUSTOMER_TYPE) =
        (SELECT B.CITY_NAME, B.CUSTOMER_TYPE
           FROM TMP_CUST_CITY B
          WHERE B.CUSTOMER_ID = A.CUSTOMER_ID)
 WHERE EXISTS (SELECT 1 FROM TMP_CUST_CITY B WHERE B.CUSTOMER_ID = A.CUSTOMER_ID);
 --缺陷:就是对表B进行两遍扫描

四.内联视图更新 与 merge更新

UPDATE (SELECT T1.FMONEY FMONEY1, T2.FMONEY FMONEY2 FROM T1, T2 WHERE T1.FNAME = T2.FNAME) T
   SET FMONEY1 = FMONEY2;

MERGE INTO T1
USING (SELECT T2.FNAME, T2.FMONEY FROM T2) T
ON (T.FNAME = T1.FNAME)
WHEN MATCHED THEN
    UPDATE SET T1.FMONEY = T.FMONEY;

五.特殊情况的优化：

因为B表的纪录只有A表的20-30%的纪录数，且 A表使用INDEX的情况 使用cursor也许会比关联update带来更好的性能：

DECLARE
    SET SERVEROUTPUT ON DECLARE CURSOR CITY_CUR IS
        SELECT CUSTOMER_ID, CITY_NAME FROM TMP_CUST_CITY ORDER BY CUSTOMER_ID;
BEGIN
    FOR MY_CUR IN CITY_CUR LOOP
        UPDATE CUSTOMERS
           SET CITY_NAME = MY_CUR.CITY_NAME
         WHERE CUSTOMER_ID = MY_CUR.CUSTOMER_ID;
        /** 此处也可以单条/分批次提交，避免锁表情况 **/
    -- if mod(city_cur%rowcount,10000)=0 then
    -- dbms_output.put_line('----');
    -- commit;
    -- end if;
    END LOOP;
END;

六.关联update的一个特例以及性能再探讨

在oracle的update语句语法中，除了可以update表之外，也可以是视图，所以有以下1个特例：

UPDATE (SELECT A.CITY_NAME, B.CITY_NAME AS NEW_NAME
           FROM CUSTOMERS A, TMP_CUST_CITY B
          WHERE B.CUSTOMER_ID = A.CUSTOMER_ID)
   SET CITY_NAME = NEW_NAME;

这样能避免对B表或其索引的2次扫描，但前提是 A(customer_id) b(customer_id)必需是unique index或primary key