sql如下：
反链接
SELECt R.ORDER_NO
FROM KKTRIBE_CORE_base.ITEM_ORDER R
WHERe R.ALLOCATE_COMMISSION_STATUS = 'FINISH'
AND R.ORDER_NO NOT IN (SELECt ORDER_NO FROM (SELECt B.ORDER_NO, COUNT(*) COUNT_NUM
FROM KKTRIBE_CORE_base.ORDER_ALLOCATE_COM_FUND_BILL B
WHERe B.STATUS NOT IN ('FINISH')
GROUP BY B.ORDER_NO) T
WHERe T.COUNT_NUM != 0);
执行计划：
可以一眼定位问题,至于优化器选取这样的方式显然不好啊，一般not in，not exists中的子查询有group by，union all，union，start with connect by都会产生filter，但是此sql的group by外面还一层，不应该，那么我们改sql好了。
改成SQL如下：
SELECt R.ORDER_NO
FROM KKTRIBE_CORE_base.ITEM_ORDER R
LEFT JOIN (SELECt ORDER_NO
FROM (SELECt B.ORDER_NO, COUNT(*) COUNT_NUM
FROM KKTRIBE_CORE_base.ORDER_ALLOCATE_COM_FUND_BILL B
WHERe B.STATUS NOT IN ('FINISH')
GROUP BY B.ORDER_NO) T
WHERe T.COUNT_NUM != 0) X
ON R.ORDER_NO = X.ORDER_NO
WHERe R.ALLOCATE_COMMISSION_STATUS = 'READY'
AND X.ORDER_NO IS NULL;
执行计划：
sql从10分钟到1秒不到，秒出
还有一个例子如下：
SELECt *
FROM (SELECt ID,
GROUP_ID,
MESSAGE_SEQUENCE,
GMT_CREATE,
GMT_MODIFIED,
SYSTEM_DELETED
FROM IM_CORE_base.IMC_GROUP_MESSAGE GM
WHERe GM.SYSTEM_DELETeD = 0
AND NOT EXISTS
(SELECT 1
FROM (SELECt MB.ID
FROM IM_CORE_base.IMC_MESSAGE_TYPE_CONFIG MTC,
IM_CORE_base.IMC_MESSAGE_base MB
WHERe MTC.TYPE_CODE IN
('GROUP_MESSAGE_DELETe_TEXT', 'IMAGE_VIEWABLE')
AND MB.TYPE_ID = MTC.ID) TMP
WHERe TMP.ID = GM.ID)
AND GROUP_ID = '10064002684129898100120010059153'
AND MESSAGE_SEQUENCE
ORDER BY MESSAGE_SEQUENCE DESC)
WHERe ROWNUM <= 22;
SELECT *
FROM (SELECt *
FROM (SELECt ID,
GROUP_ID,
MESSAGE_SEQUENCE,
GMT_CREATE,
GMT_MODIFIED,
SYSTEM_DELETED
FROM IM_CORE_base.IMC_GROUP_MESSAGE GM
WHERe GM.SYSTEM_DELETeD = 0
AND GROUP_ID = '10064002684129898100120010059153'
AND MESSAGE_SEQUENCE
ORDER BY MESSAGE_SEQUENCE DESC) X
LEFT JOIN (SELECT MB.ID
FROM IM_CORE_base.IMC_MESSAGE_TYPE_CONFIG MTC,
IM_CORE_base.IMC_MESSAGE_base MB
WHERe MTC.TYPE_CODE IN
('GROUP_MESSAGE_DELETE_TEXT', 'IMAGE_VIEWABLE')
AND MB.TYPE_ID = MTC.ID) TMP
ON TMP.ID = X.ID
WHERe TMP.ID IS NULL)
WHERe ROWNUM <= 22;
| 作者简介
姚崇·云趣科技高级数据库技术专家