如何加快海量表的更新查询
我目前正在将我们现有的一个数据库转移到一个新的本体上。数据库遵循星型模式，其中observation_fact是星表的中心，concept_dimension是查找表。为了转移到新的本体，我需要用稍微不同的代码替换concept_cd中的observation_fact，以便匹配新本体中的concept_cds。
我已经尝试过编写一个更新查询来完成这个迁移，但是它已经运行了5天，我不认为它很快就会完成。我已经在concept_cd上索引了两个相关的表。
这是我最初编写的查询：
Update observation_fact ofact
Set concept_cd = q.cd
From (Select ofact2.ctid, Case
When split_part(ofact2.concept_cd, ':', 1) = 'ICD10-CM' Then replace(ofact2.concept_cd, 'ICD10-CM:', 'ICD10CM:')
When split_part(ofact2.concept_cd, ':', 1) = 'ICD10-PCS' Then replace(ofact2.concept_cd, 'ICD10-PCS:', 'ICD10PCS:')
When split_part(ofact2.concept_cd, ':', 1) = 'ICD9' And cdim.concept_path like '\\i2b2\\Diagnoses\\%' Then replace(ofact2.concept_cd, 'ICD9:', 'ICD9CM:')
When split_part(ofact2.concept_cd, ':', 1) = 'ICD9' And cdim.concept_path like '\\i2b2\\Procedures\\%' Then replace(ofact2.concept_cd, 'ICD9:', 'ICD9PROC:')
End as cd
From observation_fact ofact2
Left Outer Join concept_dimension_bak cdim
On ofact2.concept_cd = cdim.concept_cd
) as q
Where ofact.ctid = q.ctid;编写起来很尴尬，因为observation_fact没有真正的主键或复合键，因此我不得不使用ctid。另外，我使用了observation_fact两次，从这个答案中，Speed up Postgres Update on Large Table，我知道这是一个不好的主意，可能是问题的一部分。我使用左外部联接，因为observation_fact中的某些observation_fact不存在于concept_dimension_bak中。正如您可以看到的，ICD10替换非常容易，但是对于ICD9，我需要在旧的concept_dimension表中查找代码，以便确定它是哪种类型的代码，并相应地替换它。
我期望此更新查询对observation_fact中的任何行执行适当的替换，在这些行中，case语句匹配，而忽略其他所有内容。
回答 2
Stack Overflow用户
发布于 2019-07-16 20:37:43
与其更新表，不如尝试使用SQL中的逻辑创建一个新表，这样会更快。创建新表后，可以重命名旧表，并将新表重命名为observation_fact
重申1.创建新表
insert into observation_fact_new
select ...
from observation_fact- 重命名旧表，执行正常检查
alter table observation_fact rename to observation_fact_old- 将新表重命名为observation_fact
alter table observation_fact_new rename to observation_fact检查和测试完成后，删除旧表
drop table observation_fact_old
Stack Overflow用户
发布于 2019-07-16 20:36:57
首先，更新表中的所有行需要时间。有时，用所有修改过的数据创建一个新表、截断原始表并重新加载它会更快。
第二，您要引用observation_fact两次，但这似乎没有必要。我觉得这能做你想做的事
update observation_fact ofact
set concept_cd = (case when split_part(ofact.concept_cd, ':', 1) = 'ICD10-CM'
then replace(ofact.concept_cd, 'ICD10-CM:', 'ICD10CM:')
when split_part(ofact.concept_cd, ':', 1) = 'ICD10-PCS'
then replace(ofact.concept_cd, 'ICD10-PCS:', 'ICD10PCS:')
when split_part(ofact.concept_cd, ':', 1) = 'ICD9' And cdim.concept_path like '\\i2b2\\Diagnoses\\%'
then replace(ofact.concept_cd, 'ICD9:', 'ICD9CM:')
when split_part(ofact.concept_cd, ':', 1) = 'ICD9' And cdim.concept_path like '\\i2b2\\Procedures\\%'
then replace(ofact.concept_cd, 'ICD9:', 'ICD9PROC:')
end) as cd
from concept_dimension_bak cdim
where ofact.concept_cd = cdim.concept_cd;您可能需要将不匹配的值设置为NULL。
https://stackoverflow.com/questions/57065014
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号