替换UPDATE SQL与Drop&使用默认值添加COLUMN吗？ [英] Replacing UPDATE SQL vs Drop & Add COLUMN with default value?

问题描述

我需要用固定的文本更新RDBMS表（ PostgresSQL ）的所有行的文本列。目前，表有大约70万条记录，但预计将会增长。使用以下查询，

I have a requirement to update a text column for all rows of a RDBMS table (PostgresSQL) with a fixed text. Currently, table has around 700k records but that is expected to grow. SpringJDBC batch update is slow with following query,

UPDATE TABLE TABLENAME SET columnname="FIXED VALUE"

此列为可空。是否建议用这两个步骤替换此UPDATE语句？

1。首先从表

2删除此列。将列读到表中，将默认值指定为固定值。

我测试了代码，它的执行速度比UPDATE语句快得多。

我只是想问大家，再次删除和添加列的方法是否有负面影响？

我正在通过 SpringJDBC

This columns is NULLABLE. Is it advisable to replace this single UPDATE statement with these two steps?

1. First drop this column from table

2.Readd the column to table specifying default value to be "FIXED VALUE"

I tested code and its very faster than UPDATE statement.

I just wanted to ask you folks if there is any negative side to the approach of dropping and adding column again?

I am running these SQLs in Java via SpringJDBC

推荐答案

postgresql的问题是每次更新都需要删除和插入操作。这样做70万次，将会非常缓慢，并且如果该字段具有一个索引甚至会更糟。

The problem with postgresql is every update require a delete and an insert operation. Do that 700k times and will be very slow and if that field have one index can be even worst.

我看不到您的方法有任何问题。我什至重新创建了整个表以进行一些更新。但是您必须小心，没有其他人正在使用该表或任何fk关系。

I dont see any problem with your aproach. I even recreate the whole table for some updates. But you have to be carefull no one else is using that table or are any fk relationship.

CREATE table_backup AS 
   SELECT function(field1), function(field2) ....

DROP table_current;

RENAME table_backup to table_current;

CREATE INDEX and CONSTRAINS TO table_current;

这篇关于替换UPDATE SQL与Drop&使用默认值添加COLUMN吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！