开发不会用exists.txt. 无语。。。。。。。。

lfree

开发不会用exists.txt

--最近几天在做优化工作，无意中发现一条：

sql_id=gmg7wsyv1pjm7
SELECT DISTINCT CP_LJSD.SDBH
                 ,CP_LJSD.LJBH
                 ,CP_LJSD.CKLX
                 ,CP_LJSD.XSMC AS XSMC
                 ,CP_LJSD.RQXH
    FROM CP_ZXJL, CP_ZLHD, CP_LJSD
   WHERE     (CP_ZXJL.HDBH = CP_ZLHD.HDBH)
         AND (CP_ZXJL.SDBH = CP_LJSD.SDBH)
         AND (    (CP_ZXJL.DQZT = :"SYS_B_0")
              AND (CP_ZXJL.YSXH = :"SYS_B_1")
              AND (CP_ZXJL.BRLJ = :al_brlj)
              AND (CP_ZXJL.ZCWC <= :adt_date OR CP_ZXJL.ZZKS <= :adt_date))
ORDER BY CP_LJSD.RQXH;

--注意到关键字DISTINCT，很明显显示的字段全部来自CP_LJSD，这个是使用半连接exists的经典例子，应该改写如下：

SELECT
        CP_LJSD.SDBH
        ,CP_LJSD.LJBH
        ,CP_LJSD.CKLX
        ,CP_LJSD.XSMC AS XSMC
        ,CP_LJSD.RQXH
    FROM CP_LJSD
   WHERE EXISTS
            (SELECT 1
               FROM CP_ZXJL, CP_ZLHD
              WHERE     (CP_ZXJL.HDBH = CP_ZLHD.HDBH)
                    AND (CP_ZXJL.SDBH = CP_LJSD.SDBH)
                    AND (    (CP_ZXJL.DQZT = :"SYS_B_0")
                         AND (CP_ZXJL.YSXH = :"SYS_B_1")
                         AND (CP_ZXJL.BRLJ = :al_brlj)
                         AND (   CP_ZXJL.ZCWC <= :adt_date
                              OR CP_ZXJL.ZZKS <= :adt_date)))
ORDER BY CP_LJSD.RQXH;

--以上是上个星期的事情，我想既然出现1次，应该不至这样一次的错误，我使用toad检查字串串DISTINCT，结果我真的很无语。。


--sql_id=cfjq09vz71s5y

SELECT DISTINCT
       YF_YFLB.YFMC AS KSMC, YF_YFLB.YFSB AS KSDM, YF_YFLB.PYDM AS PYDM
  FROM YF_DB01
      ,YF_DB02
      ,YK_TYPK
      ,YF_YFLB
WHERE     (YF_DB02.SQYF = YF_DB01.SQYF)
       AND (YF_DB02.SQDH = YF_DB01.SQDH)
       AND (YF_DB02.YPXH = YK_TYPK.YPXH)
       AND (YF_DB01.SQYF = YF_YFLB.YFSB)
       AND (YF_DB01.CKBZ = :"SYS_B_0")
UNION ALL
SELECT DISTINCT
       GY_KSDM.KSMC AS KSMC, GY_KSDM.KSDM AS KSDM, GY_KSDM.PYDM AS PYDM
  FROM YF_CK01
      ,YF_CK02
      ,YK_TYPK
      ,GY_KSDM
WHERE     (YF_CK01.YFSB = YF_CK02.YFSB)
       AND (YF_CK01.CKDH = YF_CK02.CKDH)
       AND (YF_CK01.CKFS = YF_CK02.CKFS)
       AND (YF_CK01.KSDM = GY_KSDM.KSDM)
       AND (YF_CK02.YPXH = YK_TYPK.YPXH)

--我看了这个语句的平均每次的逻辑读18670。

SELECT YF_YFLB.YFMC AS KSMC, YF_YFLB.YFSB AS KSDM, YF_YFLB.PYDM AS PYDM
  FROM YF_YFLB
WHERE EXISTS
          (SELECT 1
             FROM YF_DB01, YF_DB02, YK_TYPK
            WHERE     (YF_DB02.SQYF = YF_DB01.SQYF)
                  AND (YF_DB02.SQDH = YF_DB01.SQDH)
                  AND (YF_DB02.YPXH = YK_TYPK.YPXH)
                  AND (YF_DB01.SQYF = YF_YFLB.YFSB)
                  AND (YF_DB01.CKBZ = 1))
UNION ALL
SELECT GY_KSDM.KSMC AS KSMC, GY_KSDM.KSDM AS KSDM, GY_KSDM.PYDM AS PYDM
  FROM GY_KSDM
WHERE EXISTS
          (SELECT /*+  PUSH_SUBQ   */ 1
             FROM YF_CK01, YF_CK02, YK_TYPK
            WHERE     (YF_CK01.YFSB = YF_CK02.YFSB)
                  AND (YF_CK01.CKDH = YF_CK02.CKDH)
                  AND (YF_CK01.CKFS = YF_CK02.CKFS)
                  AND (YF_CK01.KSDM = GY_KSDM.KSDM)
                  AND (YF_CK02.YPXH = YK_TYPK.YPXH));
Plan hash value: 2520417422

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                          |      1 |        |       |   320 (100)|          |    259 |00:00:00.01 |    2443 |       |       |          |
|   1 |  UNION-ALL                      |                          |      1 |        |       |            |          |    259 |00:00:00.01 |    2443 |       |       |          |
|*  2 |   FILTER                        |                          |      1 |        |       |            |          |     79 |00:00:00.01 |     748 |       |       |          |
|   3 |    TABLE ACCESS STORAGE FULL    | YF_YFLB                  |      1 |     88 |  2904 |     3   (0)| 00:00:01 |     88 |00:00:00.01 |       7 |  1025K|  1025K|          |
|   4 |    NESTED LOOPS                 |                          |     88 |      3 |    87 |     7   (0)| 00:00:01 |     79 |00:00:00.01 |     741 |       |       |          |
|   5 |     NESTED LOOPS                |                          |     88 |      3 |    72 |     7   (0)| 00:00:01 |     79 |00:00:00.01 |     583 |       |       |          |
|   6 |      TABLE ACCESS BY INDEX ROWID| YF_DB02                  |     88 |   1642 | 21346 |     4   (0)| 00:00:01 |     80 |00:00:00.01 |     344 |       |       |          |
|*  7 |       INDEX RANGE SCAN          | I_YF_DB02_SQDH           |     88 |   1928 |       |     3   (0)| 00:00:01 |     80 |00:00:00.01 |     264 |  1025K|  1025K|          |
|*  8 |      TABLE ACCESS BY INDEX ROWID| YF_DB01                  |     80 |      1 |    11 |     1   (0)| 00:00:01 |     79 |00:00:00.01 |     239 |       |       |          |
|*  9 |       INDEX UNIQUE SCAN         | PK_YF_DB01               |     80 |      1 |       |     0   (0)|          |     80 |00:00:00.01 |     159 |  1025K|  1025K|          |
|* 10 |     INDEX UNIQUE SCAN           | PK_YK_TYPK               |     79 |      1 |     5 |     0   (0)|          |     79 |00:00:00.01 |     158 |  1025K|  1025K|          |
|* 11 |   TABLE ACCESS STORAGE FULL     | GY_KSDM                  |      1 |    180 |  4320 |     3   (0)| 00:00:01 |    180 |00:00:00.01 |    1695 |  1025K|  1025K|          |
|  12 |    NESTED LOOPS                 |                          |    428 |      3 |   114 |     6   (0)| 00:00:01 |    180 |00:00:00.01 |    1688 |       |       |          |
|  13 |     NESTED LOOPS                |                          |    428 |      3 |    99 |     6   (0)| 00:00:01 |    180 |00:00:00.01 |    1328 |       |       |          |
|  14 |      TABLE ACCESS BY INDEX ROWID| YF_CK01                  |    428 |     29 |   464 |     2   (0)| 00:00:01 |    180 |00:00:00.01 |     788 |       |       |          |
|* 15 |       INDEX RANGE SCAN          | I_YF_CK01_KSDM           |    428 |     28 |       |     1   (0)| 00:00:01 |    180 |00:00:00.01 |     608 |  1025K|  1025K|          |
|  16 |      TABLE ACCESS BY INDEX ROWID| YF_CK02                  |    180 |      2 |    34 |     2   (0)| 00:00:01 |    180 |00:00:00.01 |     540 |       |       |          |
|* 17 |       INDEX RANGE SCAN          | I_YF_CK02_YFSB_CKFS_CKDH |    180 |      2 |       |     1   (0)| 00:00:01 |    180 |00:00:00.01 |     360 |  1025K|  1025K|          |
|* 18 |     INDEX UNIQUE SCAN           | PK_YK_TYPK               |    180 |      1 |     5 |     0   (0)|          |    180 |00:00:00.01 |     360 |  1025K|  1025K|          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--Buffers=2443.

--还有1大堆，看来不至一个人，而是一个团队再犯这个错误，这个就是国内IT的现状,可悲！！！！
--我自己现在发现，都觉得丢脸，整个团队没发现，真的很无语.....................

lfree

帖几个图

XQKA

这是普遍现象，现在的程序猿会写sql99，后台是oracle数据库，他就敢说自己是oracle开发。

zergduan

前提是
YF_YFLB.YFMC AS KSMC, YF_YFLB.YFSB AS KSDM, YF_YFLB.PYDM AS PYDM
和
GY_KSDM.KSMC AS KSMC, GY_KSDM.KSDM AS KSDM, GY_KSDM.PYDM AS PYDM
里面有一个是主键或者唯一列呀~

要不然并不等价吧？

2009532140

这很正常吧..
我们这边不管什么子查询寻都是用exists 来搞是不是 有点意思？

wolfop

还在用10G么？11GR2基本没必要用exist

lfree

zergduan 发表于 2015-12-14 13:25
前提是
YF_YFLB.YFMC AS KSMC, YF_YFLB.YFSB AS KSDM, YF_YFLB.PYDM AS PYDM
和

不在于这里，而是使用distinct来避免重复。

lfree

wolfop 发表于 2015-12-14 13:53
还在用10G么？11GR2基本没必要用exist

11g ，我觉得你没有明白我的意思。

zergduan

lfree 发表于 2015-12-14 14:48
不在于这里，而是使用distinct来避免重复。

我明白你的意思，但是你用exsits代替distinct的前提是：查询结果列中必须有唯一列~

否则用exsits查询后会有重复的值，与原来SQL并不等价呀

zergduan

lfree 你们再用extradata？ 啥型号，整套还是半套？ 感觉和传统机器比，性价比如何？