优化大型表上的sql更新

wztqucjr 于 2021-07-29 发布在 Java

关注(0)|答案(1)|浏览(326)

我需要你帮助对一个大表（大约50亿行-基于oracle11g）进行sql更新。我使用sql plus从shell脚本生成并执行更新，但执行查询需要花费大量时间，有没有办法加快更新速度：

Update My_table
Set CODE = ‘F’ where  date=’2020/06/05’ and ID=’87444’  and GROUP=’GGG’ and NUM_FAC=’15444’
Update My_table
Set CODE = ‘D’ where  date=’2020/06/01’ and ID=’85544’  and GROUP=’GGG’ and NUM_FAC=’55555’
Update My_table
Set CODE = ‘I’  where  date=’2020/06/03’ and ID=’85544’  and GROUP=’GGG’ and NUM_FAC=’55555’
….. Plusieurs lignes  
Commit ;  ```
Exit SUCCESS
Thank you in advance for your help.

sql oracle-sqldeveloper oracle11g

来源：https://stackoverflow.com/questions/62299133/optimization-sql-update-on-a-large-table

1条答案

按热度按时间

1tuwyuhd1#

将常见条件移至 WHERE 子句，然后使用 CASE 表达式：

Update My_table
    Set CODE = (CASE WHEN date = '2020/06/05' THEN 'F'
                     WHEN date = '2020/06/01' THEN 'D'
                     WHEN date = '2020/06/03' THEN 'C'
                     . . .
                END)
WHERE ID = '85544'  and GROUP = 'GGG' and NUM_FAC = '55555' AND
      DATE IN ('2020/06/05', '2020/06/01', '2020/06/03', . . . );

赞(0）回复(0）举报 2021-07-29

我来回答

优化大型表上的sql更新

1条答案

相关问题

热门标签

最新问答