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

帮忙优化条语句，头疼一下午了
两表数据都在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_NUMBERVARCHAR2(30)
START_TIMEDATE
DURATION NUMBER(18)
---------------------
两个表的THIRD_NUMBER字段都建有索引
---------------------

UPDATE STATEMENT, GOAL = ALL_ROWS192526169370019233257807
 UPDATESTL_USRT_YD_USAGE_EVENTS_OLD_30
  FILTER
  TABLE ACCESS FULLSTL_USRT_YD_USAGE_EVENTS_OLD_3017943506186818728911617048
  TABLE ACCESS BY INDEX ROWIDSTL_USRT_YD_USAGE_EVENTS_NEW_303805103503801
  INDEX RANGE SCANSTL_USRIND_YD_USAGE_EVENTS_NEW_30_0122410722



[解决办法]
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)

查看更多 下一篇