合 采用MERGE 语句的非关联形式提升SQL性能
今天照例巡查垃圾sql时,发现一个跑了很长时间的sql,且其执行计划也非常的大,这个sql非常可疑,得排查排查:
第一步,照例查询内存中的执行计划:
SELECT *** FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('17t226txddfy5',0,'advanced'));**
可以看出执行计划的cost花费非常的大,且Predicate Information(即谓语)部分全是filter过滤的,怎么会没有access访问呢???????拿出其具体的sql语句:
MERGE INTO FLN_RCW_CM_ENGINE_BLACK_PURIFI BLACK_PURIFI
USING (SELECT *****
FROM (SELECT T.*,
ROW_NUMBER() OVER(PARTITION BY CERTIFICATION_NO_18, INFO_CHARACTER, FILTER_CONFIG_ID ORDER BY CERTIFICATION_NO_18) NUM
FROM FLN_RCW_CM_T_ENGINE_BLACK T) T
WHERE NUM = 1) BLACK
ON (BLACK_PURIFI.CERTIFICATION_NO_18 = BLACK.CERTIFICATION_NO_18 AND BLACK_PURIFI.INFO_CHARACTER = BLACK.INFO_CHARACTER AND (BLACK.FILTER_CONFIG_ID IS NULL OR BLACK_PURIFI.FILTER_CONFIG_ID = BLACK.FILTER_CONFIG_ID) AND BLACK_PURIFI.DATE_DELETE IS NULL)
WHEN MATCHED THEN
UPDATE
SET BATCH_DATE = :B1,
FLAG_DELETE = 'N',
PURIFICATION_DATE = :B1
WHEN NOT MATCHED THEN
INSERT
VALUES
(:B1,