[20151212]优化sql语句要注意关键字DISTINCT.txt
--做sql语句优化要特别注意带DISTINCT语句，有一些情况往往是开发写错或者写少了连接条件，或者没有用exists等关键字。
--举最近优化生产系统遇到的例子：
--显示的问题，我做了格式化处理：
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;
--很明显如果看我以前写的链接：http://blog.itpub.net/267265/viewspace-1852195/
--可以这些代码可能出自一个人之手，他的风格明显就是把需要连接的表列出来，在写出sql语句。
--正确的写法应该这样。
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;
--sql_id=d3g3mkq448tr8
SELECT DISTINCT zy_brry.brch
,zy_brry.zyh
,zy_brry.mzhm
,zy_brry.brxm
,ys_zy_jbzd.mszd
,emr_zkbz.bzmc
, :"SYS_B_0" AS CP_BRLJ
FROM zy_brry
,ys_zy_jbzd
,emr_zkbz
,EMR_BZGL
,GY_JBBM
WHERE zy_brry.cypb = :"SYS_B_1"
AND TO_CHAR (zy_brry.zyh) = ys_zy_jbzd.jzhm
AND ys_zy_jbzd.zdlb = :"SYS_B_2"
AND ys_zy_jbzd.zdsj >= TRUNC (SYSDATE - :"SYS_B_3")
AND ys_zy_jbzd.zfbz = :"SYS_B_4"
AND GY_JBBM.ICD9 = EMR_BZGL.GLBM
AND EMR_BZGL.cpbz = :"SYS_B_5"
AND EMR_BZGL.bzbh = emr_zkbz.bzbh
AND ys_zy_jbzd.mszd LIKE :"SYS_B_6" || GY_JBBM.jbmc || :"SYS_B_7"
AND (brks = :al_brks OR :al_brks = :"SYS_B_8")
AND ys_zy_jbzd.tjbz = :"SYS_B_9"
ORDER BY brch, mzhm;
--显示根本没有包括EMR_BZGL，GY_JBBM表。
--sql_id=fuk298uwgb3t9
SELECT DISTINCT
ZY_BQYZ.ZYH
,ZY_BRRY.BRCH
,ZY_BRRY.ZYHM
,ZY_BRRY.BRXM
,ZY_BRRY.BRXZ
,ZY_BRRY.ZLXZ
, (CASE
WHEN REGEXP_LIKE (ZY_BRRY.BRCH, :"SYS_B_00", :"SYS_B_01")
THEN
LPAD (ZY_BRRY.BRCH, :"SYS_B_02", :"SYS_B_03")
ELSE
LPAD (ZY_BRRY.BRCH, :"SYS_B_04", :"SYS_B_05")
END)
AS PLSX_CH
FROM ZY_BQYZ, ZY_BRRY
WHERE ZY_BQYZ.ZYH = ZY_BRRY.ZYH
AND (ZY_BQYZ.SRKS = ( :al_hsql))
AND (ZY_BQYZ.JFBZ = :"SYS_B_06" OR ZY_BQYZ.JFBZ = :"SYS_B_07")
AND (ZY_BQYZ.XMLX > :"SYS_B_08")
AND (ZY_BQYZ.LSBZ = :"SYS_B_09" OR ZY_BQYZ.LSBZ = :"SYS_B_10")
AND (ZY_BQYZ.YZPB = :"SYS_B_11")
AND (ZY_BQYZ.SYBZ <> :"SYS_B_12")
AND ( ZY_BQYZ.QRSJ IS NULL
OR (ZY_BQYZ.TZSJ > ZY_BQYZ.QRSJ)
OR (ZY_BQYZ.TZSJ IS NULL))
AND (ZY_BQYZ.QRSJ <= :ad_today OR ZY_BQYZ.QRSJ IS NULL)
AND ZY_BQYZ.ZXBQ IS NULL
ORDER BY PLSX_CH, ZY_BRRY.BRCH
--注意一个细节DISTINCT后面跟的是ZY_BQYZ.ZYH，视乎不是ZY_BRRY表的字段，实际上看看后面的连接条件ZY_BQYZ.ZYH = ZY_BRRY.ZYH就明白，
--完成可以换成ZY_BRRY.ZYH，这样实际上就是一个使用半连接exists的例子。
--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)
--我看了这个语句的平均每次的逻辑读18619.
> @ bind_cap cfjq09vz71s5y
C200
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/* select ksdm, ksmc from gy_ksdm where bqsy='Y' or zysy='Y' */ SELECT distinct YF_YFLB.YFMC AS KSMC, YF_YFLB.YFSB AS KSDM, YF_YFLB.PYDM AS PYDM FROM YF_DB01, YF_DB02, YK_T
YPK, 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_D
B01.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)
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING
------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- -------------
cfjq09vz71s5y 0 YES :SYS_B_0 1 22 2015-12-09 17:03:23 NUMBER 1
Plan hash value: 429408998
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 4967 (100)| | 259 |00:00:00.27 | 20343 | | | |
| 1 | UNION-ALL | | 1 | | | | | | 259 |00:00:00.27 | 20343 | | | |
| 2 | HASH UNIQUE | | 1 | 152K| 9222K| 9M| 2957 (1)| 00:00:36 | 79 |00:00:00.23 | 3041 | 16M| 5692K| 4596K (0)|
|* 3 | HASH JOIN | | 1 | 152K| 9222K| | 831 (1)| 00:00:10 | 155K|00:00:00.20 | 3041 | 4904K| 4904K| 1649K (0)|
| 4 | INDEX STORAGE FAST FULL SCAN | PK_YK_TYPK | 1 | 2737 | 13685 | | 3 (0)| 00:00:01 | 2737 |00:00:00.01 | 11 | 1025K| 1025K| |
|* 5 | HASH JOIN | | 1 | 152K| 8478K| | 828 (1)| 00:00:10 | 155K|00:00:00.16 | 3030 | 2455K| 2455K| 1253K (0)|
| 6 | TABLE ACCESS STORAGE FULL | YF_YFLB | 1 | 88 | 2904 | | 3 (0)| 00:00:01 | 88 |00:00:00.01 | 6 | 1025K| 1025K| |
|* 7 | HASH JOIN | | 1 | 152K| 3570K| | 825 (1)| 00:00:10 | 155K|00:00:00.12 | 3024 | 5615K| 5615K| 4192K (0)|
| 8 | JOIN FILTER CREATE | :BF0000 | 1 | 53855 | 578K| | 139 (1)| 00:00:02 | 54257 |00:00:00.02 | 504 | | | |
|* 9 | TABLE ACCESS STORAGE FULL | YF_DB01 | 1 | 53855 | 578K| | 139 (1)| 00:00:02 | 54257 |00:00:00.01 | 504 | 1025K| 1025K| |
| 10 | JOIN FILTER USE | :BF0000 | 1 | 152K| 1933K| | 685 (1)| 00:00:09 | 156K|00:00:00.04 | 2520 | | | |
|* 11 | TABLE ACCESS STORAGE FULL | YF_DB02 | 1 | 152K| 1933K| | 685 (1)| 00:00:09 | 156K|00:00:00.03 | 2520 | 1025K| 1025K| |
| 12 | HASH UNIQUE | | 1 | 14882 | 901K| 1008K| 2011 (1)| 00:00:25 | 180 |00:00:00.04 | 17302 | 2908K| 2908K| 2735K (0)|
| 13 | NESTED LOOPS | | 1 | 14882 | 901K| | 1801 (1)| 00:00:22 | 15216 |00:00:00.04 | 17302 | | | |
|* 14 | HASH JOIN | | 1 | 14882 | 828K| | 1801 (1)| 00:00:22 | 15216 |00:00:00.02 | 1986 | 2326K| 2326K| 1267K (0)|
| 15 | JOIN FILTER CREATE | :BF0001 | 1 | 5063 | 197K| | 1731 (0)| 00:00:21 | 5154 |00:00:00.01 | 1734 | | | |
|* 16 | HASH JOIN | | 1 | 5063 | 197K| | 1731 (0)| 00:00:21 | 5154 |00:00:00.01 | 1734 | 2701K| 2701K| 1256K (0)|
| 17 | TABLE ACCESS STORAGE FULL | GY_KSDM | 1 | 427 | 10248 | | 3 (0)| 00:00:01 | 428 |00:00:00.01 | 6 | 1025K| 1025K| |
| 18 | TABLE ACCESS BY INDEX ROWID| YF_CK01 | 1 | 5063 | 81008 | | 1728 (0)| 00:00:21 | 5191 |00:00:00.01 | 1728 | | | |
|* 19 | INDEX FULL SCAN | I_YF_CK01_KSDM | 1 | 5191 | | | 10 (0)| 00:00:01 | 5191 |00:00:00.01 | 10 | 1025K| 1025K| |
| 20 | JOIN FILTER USE | :BF0001 | 1 | 16178 | 268K| | 69 (0)| 00:00:01 | 16075 |00:00:00.01 | 252 | | | |
|* 21 | TABLE ACCESS STORAGE FULL | YF_CK02 | 1 | 16178 | 268K| | 69 (0)| 00:00:01 | 16075 |00:00:00.01 | 252 | 1025K| 1025K| |
|* 22 | INDEX UNIQUE SCAN | PK_YK_TYPK | 15216 | 1 | 5 | | 0 (0)| | 15216 |00:00:00.01 | 15316 | 1025K| 1025K| |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 170966610
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 3252 (100)| | 259 |00:00:00.27 | 18660 | | | |
| 1 | UNION-ALL | | 1 | | | | | | 259 |00:00:00.27 | 18660 | | | |
| 2 | HASH UNIQUE | | 1 | 152K| 9222K| 9M| 2957 (1)| 00:00:36 | 79 |00:00:00.23 | 3041 | 16M| 5692K| 4604K (0)|
|* 3 | HASH JOIN | | 1 | 152K| 9222K| | 831 (1)| 00:00:10 | 155K|00:00:00.20 | 3041 | 4904K| 4904K| 1733K (0)|
| 4 | INDEX STORAGE FAST FULL SCAN | PK_YK_TYPK | 1 | 2737 | 13685 | | 3 (0)| 00:00:01 | 2737 |00:00:00.01 | 11 | 1025K| 1025K| |
|* 5 | HASH JOIN | | 1 | 152K| 8478K| | 828 (1)| 00:00:10 | 155K|00:00:00.16 | 3030 | 2455K| 2455K| 1244K (0)|
| 6 | TABLE ACCESS STORAGE FULL | YF_YFLB | 1 | 88 | 2904 | | 3 (0)| 00:00:01 | 88 |00:00:00.01 | 6 | 1025K| 1025K| |
|* 7 | HASH JOIN | | 1 | 152K| 3570K| | 825 (1)| 00:00:10 | 155K|00:00:00.12 | 3024 | 5615K| 5615K| 4184K (0)|
| 8 | JOIN FILTER CREATE | :BF0000 | 1 | 53855 | 578K| | 139 (1)| 00:00:02 | 54257 |00:00:00.02 | 504 | | | |
|* 9 | TABLE ACCESS STORAGE FULL | YF_DB01 | 1 | 53855 | 578K| | 139 (1)| 00:00:02 | 54257 |00:00:00.01 | 504 | 1025K| 1025K| |
| 10 | JOIN FILTER USE | :BF0000 | 1 | 152K| 1933K| | 685 (1)| 00:00:09 | 156K|00:00:00.04 | 2520 | | | |
|* 11 | TABLE ACCESS STORAGE FULL | YF_DB02 | 1 | 152K| 1933K| | 685 (1)| 00:00:09 | 156K|00:00:00.03 | 2520 | 1025K| 1025K| |
| 12 | HASH UNIQUE | | 1 | 14882 | 901K| 1008K| 295 (1)| 00:00:04 | 180 |00:00:00.04 | 15619 | 2908K| 2908K| 2735K (0)|
| 13 | NESTED LOOPS | | 1 | 14882 | 901K| | 86 (2)| 00:00:02 | 15216 |00:00:00.03 | 15619 | | | |
|* 14 | HASH JOIN | | 1 | 14882 | 828K| | 85 (0)| 00:00:02 | 15216 |00:00:00.02 | 303 | 2326K| 2326K| 1306K (0)|
| 15 | JOIN FILTER CREATE | :BF0001 | 1 | 5063 | 197K| | 16 (0)| 00:00:01 | 5154 |00:00:00.01 | 51 | | | |
|* 16 | HASH JOIN | | 1 | 5063 | 197K| | 16 (0)| 00:00:01 | 5154 |00:00:00.01 | 51 | 2701K| 2701K| 1106K (0)|
| 17 | JOIN FILTER CREATE | :BF0002 | 1 | 427 | 10248 | | 3 (0)| 00:00:01 | 428 |00:00:00.01 | 6 | | | |
| 18 | TABLE ACCESS STORAGE FULL| GY_KSDM | 1 | 427 | 10248 | | 3 (0)| 00:00:01 | 428 |00:00:00.01 | 6 | 1025K| 1025K| |
| 19 | JOIN FILTER USE | :BF0002 | 1 | 5063 | 81008 | | 13 (0)| 00:00:01 | 5154 |00:00:00.01 | 45 | | | |
|* 20 | TABLE ACCESS STORAGE FULL| YF_CK01 | 1 | 5063 | 81008 | | 13 (0)| 00:00:01 | 5154 |00:00:00.01 | 45 | 1025K| 1025K| |
| 21 | JOIN FILTER USE | :BF0001 | 1 | 16178 | 268K| | 69 (0)| 00:00:01 | 16075 |00:00:00.01 | 252 | | | |
|* 22 | TABLE ACCESS STORAGE FULL | YF_CK02 | 1 | 16178 | 268K| | 69 (0)| 00:00:01 | 16075 |00:00:00.01 | 252 | 1025K| 1025K| |
|* 23 | INDEX UNIQUE SCAN | PK_YK_TYPK | 15216 | 1 | 5 | | 0 (0)| | 15216 |00:00:00.01 | 15316 | 1025K| 1025K| |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--我改写如下：
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 | 21 | 504 | 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| |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--如果对比A-TIME。更加要命的是实际上这样写一点问题都没有：
--还有1大堆，看来不至一个人，而是一个团队再犯这个错误，这个就是国内IT的现状,可悲！！！！