优化SQL语句

Question

如果表B中按3列分组(A和B之间匹配)的记录计数小于7，则需要更新表A中的列。我编写了下面的查询，但它运行时间很长。请提出任何最佳查询或调优。

update /*+ parallel(A) */ A set A.col4=0
where exists
      (select 1 
       from B 
       where A.col1=B.col1 and A.col2=B.col2
          and A.col3=B.col3 
       group by col1,col2,col3
       having count(*) < 7)

Answer 1

尝尝这个,

MERGE INTO A 
USING (
SELECT col1, col2, col3
      FROM B
      GROUP BY col1, col2, col3
      HAVING COUNT(*) > 7
) b ON (A.col1 = b.col1 AND A.col2=b.col2 AND A.col3= b.col3)
WHEN MATCHED THEN UPDATE
SET A.col4 = 0;

在SQL：http://www.sqlfiddle.com/#!4/dcdf1/17上查看它

让我知道它是否成功！

Answer 2

我的第一个建议是在B：B(col1, col2, col3)上创建一个索引。

下一次尝试是将其转换为join。

update A
    set col4 = 0
    from (select col1, col2, col3
          from B
          group by col1, col2, col3
          having count(*) < 7
         ) B
    where A.col1 = B.col1 and A.col2 = B.col2
          and A.col3 = B.col3 ;