时间戳

首页 > 解决方案 > 如何加快海量表的更新查询

sql - 如何加快海量表的更新查询

问题描述

我目前正在将我们现有的数据库之一转移到新的本体上。数据库遵循星型模式，observation_fact 是星型的中心，concept_dimension 是查找表。为了转移到新本体，我需要用稍微不同的代码替换observation_fact 中的concept_cd，以匹配新本体中的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。我也使用了两次观察事实，从这个答案中加速 Postgres Update on Large Table我知道这是一个坏主意，可能是问题的一部分。我使用左外连接是因为observation_fact 中的一些concept_cds 在concept_dimension_bak 中不存在。如您所见，ICD10 替换非常容易，但是对于 ICD9，我需要在旧的 concept_dimension 表中查找代码，以确定它是哪种类型的代码并相应地替换它。

我希望此更新查询能够对 observation_fact 中 case 语句匹配的任何行执行适当的替换，并忽略其他所有内容。

标签： sqlpostgresql

解决方案

您应该尝试使用 SQL 中的逻辑创建一个新表，而不是更新表，这样会更快。创建新表后，您可以重命名旧表并将新表重命名为observation_fact

重申 1. 创建新表

insert into observation_fact_new 
select ...
from observation_fact
  1. 重命名旧表进行完整性检查
alter table observation_fact rename to observation_fact_old
  1. 将新表重命名为 observation_fact
alter table observation_fact_new rename to observation_fact

检查和测试完成后删除旧表

drop table observation_fact_old

推荐阅读