关联表更新Merge优化

昨天，有一位美女提出了如下需求，周末没事，在家里好好查了一下。

1 需求分析

有三张表，商品表（TBLITEM），库存表（TBLSTORAGE），库存管理表（TBLMANAGEMENT）；

每一种商品对应的处理区分是0，并且库存总量大于1000，就把库存管理表的库存区分更新为1，小于1000，更新为0。

2 数据准备

DROP TABLE TBLITEM;

DROP TABLE TBLSTORAGE;

DROP TABLE TBLMANAGEMENT;

create table TBLITEM as  select d.OWNER as ITEMNO,round(dbms_random.value) as HANDLE_FLG from dba_objects d WHERE d.owner<>'sys';

create table TBLSTORAGE as select d.OWNER as ITEMNO,trunc(dbms_random.value(0,100)) as STROR_AMOUNT from dba_objects d WHERE d.owner<>'sys' ;

create table TBLMANAGEMENT as select d.OWNER as ITEMNO,round(dbms_random.value) as STORE_KIND from dba_objects d WHERE d.owner<>'sys' ;

3 SQL改善

3.1 写法一

UPDATE TBLMANAGEMENT M

SET M.STORE_KIND=(SELECT CASE WHEN SUM(S.STROR_AMOUNT)>1000 THEN 1

                         ELSE 0 END

                  FROM TBLITEM I

                                INNER JOIN TBLSTORAGE S

                                     ON I.ITEMNO=S.ITEMNO

                              WHERE I.HANDLE_FLG=0

                                AND M.ITEMNO=I.ITEMNO

                                   AND M.ITEMNO=S.ITEMNO

                              GROUP BY S.ITEMNO)

WHERE EXISTS (SELECT 1

              FROM TBLITEM I

                            INNER JOIN TBLSTORAGE S

                              ON I.ITEMNO=S.ITEMNO

                        WHERE I.HANDLE_FLG=0

                          AND M.ITEMNO=I.ITEMNO

                             AND M.ITEMNO=S.ITEMNO);

这种写法能够实现需求，但是作为资深的老牌强迫症患者，一条SQL语句中重复扫描一张表是不能忍受的，所以有了下面异想天开的写法。

 

3.2写法二（错误）

WITH TMP AS

    (SELECT S.ITEMNO, (CASE WHEN SUM(S.STROR_AMOUNT)>1000 THEN 1

                       ELSE 0 END) AS AMOUTFLG

     FROM TBLITEM I

           INNER JOIN TBLSTORAGE S

                 ON I.ITEMNO=S.ITEMNO

        WHERE I.HANDLE_FLG=0

        GROUP BY S.ITEMNO)

UPDATE TBLMANAGEMENT M

SET M.STORE_KIND=(SELECT T.AMOUTFLG

                  FROM TMP T

                              WHERE T.ITEMNO=M.ITEMNO)

WHERE EXISTS(SELECT 1

             FROM TMP T

                      WHERE T.ITEMNO=M.ITEMNO);

这条语句直接有语法错误，如果是检索处理，用with提取公共部分，还可以，但是是更新操作，无法实现。

3.3 Merge优化

MERGE INTO TBLMANAGEMENT M

  USING (SELECT S.ITEMNO, (CASE WHEN SUM(S.STROR_AMOUNT)>1000 THEN 1

                          ELSE 0 END) AS AMOUNT_FLG

         FROM TBLITEM I

                     INNER JOIN TBLSTORAGE S

                            ON I.ITEMNO=S.ITEMNO

               WHERE I.HANDLE_FLG=0

               GROUP BY S.ITEMNO) T

    ON (M.ITEMNO=T.ITEMNO)

WHEN MATCHED THEN UPDATE SET M.STORE_KIND=T.AMOUNT_FLG;

用merge改写，避免了表的重复的扫描，物理读有181k降低到75多。IO减少一半。