join实例 oracle_【云趣科技】Oracle优化案例-用left join代替反连接 not in not exists（十）...

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;

|  作者简介

姚崇·云趣科技高级数据库技术专家

精通Oracle、MySQL等多种数据库内部机制、擅长数据库性能调优、故障诊断、数据迁移等数据库生命周期内的数据服务