帮忙优化条语句，头疼一下午了解决方案

帮忙优化条语句，头疼一下午了
两表数据都在500万以上
UPDATE T_YD_USAGE_EVENTS_OLD_30 O
  SET O.BJ = 1
 WHERE EXISTS (SELECT 1
  FROM T_YD_USAGE_EVENTS_NEW_30 N
  WHERE N.THIRD_NUMBER = O.THIRD_NUMBER
  AND ROUND(ABS(N.START_TIME - O.START_TIME) * 24 * 60 * 60) <= 0
  AND ABS(N.DURATION - O.DURATION) <= 0);

--------------------------------------------------
THIRD_NUMBER VARCHAR2(30)
START_TIME DATE
DURATION NUMBER(18)
---------------------
两个表的THIRD_NUMBER字段都建有索引
---------------------

UPDATE STATEMENT, GOAL = ALL_ROWS 19252616937 0 0 19233257807
 UPDATE STL_USR T_YD_USAGE_EVENTS_OLD_30
  FILTER
  TABLE ACCESS FULL STL_USR T_YD_USAGE_EVENTS_OLD_30 17943 5061868 187289116 17048
  TABLE ACCESS BY INDEX ROWID STL_USR T_YD_USAGE_EVENTS_NEW_30 3805 10 350 3801
  INDEX RANGE SCAN STL_USR IND_YD_USAGE_EVENTS_NEW_30_01 22 4107 22



------解决方案--------------------
ABS(N.DURATION - O.DURATION)<=0

abs取绝对值，它不可能小于0。

ABS(N.DURATION - O.DURATION)=0

等价于N.DURATION = O.DURATION

ROUND(ABS(N.START_TIME - O.START_TIME) * 24 * 60 * 60) <= 0

同理

N.START_TIME = O.START_TIME
------解决方案--------------------
这么大的数据量，我觉得用hash半连接最好
UPDATE T_YD_USAGE_EVENTS_OLD_30 O
SET O.BJ = 1
 WHERE EXISTS (SELECT /*+ hash_sj(o n)*/1
FROM T_YD_USAGE_EVENTS_NEW_30 N
WHERE N.THIRD_NUMBER = O.THIRD_NUMBER);
------解决方案--------------------

探讨

引用:
首先我也分析下你的SQL是什么目的吧，接楼上
1、ABS 是绝对值函数，所以它获取的数据肯定是>=0的才，所以ABS(N.DURATION - O.DURATION)<=0等价于ABS(N.DURATION - O.DURATION)=0，那么好比是：N.DURATION = O.DURATION

2、接着ROUND(ABS(N.START_T……

------解决方案--------------------
UPDATE T_YD_USAGE_EVENTS_OLD_30 O
SET O.BJ = 1
 WHERE EXISTS (SELECT 1
FROM T_YD_USAGE_EVENTS_NEW_30 N
WHERE N.THIRD_NUMBER = O.THIRD_NUMBER
AND ROUND(ABS(N.START_TIME - O.START_TIME) * 24 * 60 * 60) <= 0
AND ABS(N.DURATION - O.DURATION) <= 0);

先把标红的内容新建一个表:
create table t_tmp
as 
SELECT distinct O.rowid rid
FROM T_YD_USAGE_EVENTS_NEW_30 N,T_YD_USAGE_EVENTS_OLD_30 O
WHERE N.THIRD_NUMBER = O.THIRD_NUMBER
AND ROUND(ABS(N.START_TIME - O.START_TIME) * 24 * 60 * 60) <= 0
AND ABS(N.DURATION - O.DURATION) <= 0;

下面再update 一定快:
UPDATE /*+ordered use_nl(0)*/ T_YD_USAGE_EVENTS_OLD_30 O
SET O.BJ = 1
 WHERE rowid in(select rid from t_tmp)