使用另一个表进行更新后，RedShift表行将重复 [英] RedShift table rows are duplicated after updating using another table

问题描述

主表:

CREATE TABLE ca_ger.cln_trans_base (
  store_code VARCHAR(25),
  year_week varchar(25),
  division VARCHAR(25) encode lzo,
  market_share_code VARCHAR(25) encode bytedict,
  pon   VARCHAR(25) encode lzo,
  osp_price REAL encode bytedict
)
diststyle key distkey(store_code)
sortkey(store_code,year_week); 

COMMIT;

第二张表:

CREATE TABLE ca_ger.divisions(
  store_code VARCHAR(25),
  year_week varchar(25),
  division VARCHAR(25) encode lzo
)
diststyle key distkey(store_code)
sortkey(store_code,year_week); 

COMMIT; 

alter table ca_ger.cln_Trans_base
add column division(varchar 25);

加入子句:

update ca_ger.cln_trans_base 
 set division=b.division 
from ca_Ger.cln_trans_base a 
  join divisions b on a.year_week=b.year_week;

此后，我运行了vacuum和analyze.之后，我检查了统计信息表，并将行加倍.

After this, I ran vacuum and analyze. After this, I checked stats table and rows are doubled.

推荐答案

在Amazon Redshift中，

In Amazon Redshift,

Update = DELETE + INSERT

Update=DELETE+INSERT

因此您的行大小增加了一倍.因为您可能正在更新所有行.

Hence your rows size is doubled. As you might be updating all the rows.

在每次更新后或按计划进行，您应该按照注释之一的建议进行full Vaccum.

After every Update or on scheduled basis, you should be doing full Vaccum as suggested in one of comment.

VACUUM FULL cln_trans_base

它将尺寸减小到一半. 希望对您有帮助.

It will reduce the size to half. Hope it will help you.

这篇关于使用另一个表进行更新后，RedShift表行将重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！