今天在客户现场出现一个问题,后台的SQL语句执行很慢:
SELECT COUNT(DISTINCT XA.ID) AS COUNT
FROM CRM_CUSTOMERS XA
WHERE 1 = 1
AND (EXISTS (SELECT 1
FROM CRM_GROUP_TARGET XI0
WHERE 1 = 1
AND XI0.GROUPID = 4633
AND XI0.TARGETID = XA.ID
AND XI0.OPTYPE = 0) OR EXISTS
(SELECT 1
FROM CRM_GROUP_TARGET XI1
WHERE 1 = 1
AND XI1.GROUPID = 4634
AND XI1.TARGETID = XA.ID
AND XI1.OPTYPE = 0))
AND XA.ISVALID = 0
由于是同一张表自身做EXISTS操作,该表(CRM_GROUP_TARGET)数据量很大,速度很慢。
修改之后SQL如下:
SELECT COUNT(DISTINCT XA.ID) AS COUNT
FROM CRM_CUSTOMERS XA
WHERE 1 = 1
AND EXISTS(
SELECT * FROM (
(SELECT XI0.TARGETID
FROM CRM_GROUP_TARGET XI0
WHERE 1 = 1
AND XI0.GROUPID = 4633
AND XI0.OPTYPE = 0
UNION ALL
SELECT XI1.TARGETID
FROM CRM_GROUP_TARGET XI1
WHERE 1 = 1
AND XI1.GROUPID = 4634
AND XI1.OPTYPE = 0)) t where t.TARGETID = XA.ID
)
AND XA.ISVALID = 0;
使用UNION ALL之后,速度很快。问题是解决了,但是想不明白原因...
麻烦各位大侠解答一下,非常感谢~
SELECT COUNT(DISTINCT XA.ID) AS COUNT
FROM CRM_CUSTOMERS XA
WHERE 1 = 1
AND (EXISTS (SELECT 1
FROM CRM_GROUP_TARGET XI0
WHERE 1 = 1
AND XI0.GROUPID = 4633
AND XI0.TARGETID = XA.ID
AND XI0.OPTYPE = 0) OR EXISTS
(SELECT 1
FROM CRM_GROUP_TARGET XI1
WHERE 1 = 1
AND XI1.GROUPID = 4634
AND XI1.TARGETID = XA.ID
AND XI1.OPTYPE = 0))
AND XA.ISVALID = 0
由于是同一张表自身做EXISTS操作,该表(CRM_GROUP_TARGET)数据量很大,速度很慢。
修改之后SQL如下:
SELECT COUNT(DISTINCT XA.ID) AS COUNT
FROM CRM_CUSTOMERS XA
WHERE 1 = 1
AND EXISTS(
SELECT * FROM (
(SELECT XI0.TARGETID
FROM CRM_GROUP_TARGET XI0
WHERE 1 = 1
AND XI0.GROUPID = 4633
AND XI0.OPTYPE = 0
UNION ALL
SELECT XI1.TARGETID
FROM CRM_GROUP_TARGET XI1
WHERE 1 = 1
AND XI1.GROUPID = 4634
AND XI1.OPTYPE = 0)) t where t.TARGETID = XA.ID
)
AND XA.ISVALID = 0;
使用UNION ALL之后,速度很快。问题是解决了,但是想不明白原因...
麻烦各位大侠解答一下,非常感谢~
用OR之后oracle会走全表扫描,而union all之后子查询返回一个结果集,然后走索引扫描,开销比全表扫描小很多。