Oracle SQL Update查询需要几天的时间来更新 [英] Oracle SQL Update query takes days to update

问题描述

我正在尝试根据来自源的记录来更新目标表中的记录.例如，如果输入记录存在于目标表中，我将在目标中更新它们，否则我将简单地插入.我的来源中有超过一百万条记录，而我的目标中有四千六百万条记录.目标表根据日历键进行分区.我使用Informatica实现了整个逻辑.我发现Informatica代码非常适合查看Informatica会话日志，但是其更新需要很长时间(更新一百万条记录需要5天以上的时间).

I am trying to update a record in the target table based on the record coming in from source. For instance, if the incoming record is present in the target table I would update them in the target else I would simply insert. I have over one million records in my source while my target has 46 million records. The target table is partitioned based on calendar key. I implement this whole logic using Informatica. I find that the Informatica code is perfectly fine looking at the Informatica session log but its in the update it takes long time (more than 5 days to update one million records).

关于该方案可以用来提高性能的任何建议?

Any suggestions as to what can be done on the scenario to improve the performance?

推荐答案

您可以尝试

  1  MERGE
  2     INTO  target_table tgt
  3     USING source_table src
  4     ON  ( src.object_id = tgt.object_id )
  5  WHEN MATCHED
  6  THEN
  7     UPDATE
  8     SET   tgt.object_name = src.object_name
  9     ,     tgt.object_type = src.object_type
 10  WHEN NOT MATCHED
 11  THEN
 12     INSERT ( tgt.object_id
 13            , tgt.object_name
 14            , tgt.object_type )
 15     VALUES ( src.object_id
 16            , src.object_name
 17            , src.object_type );

一开始的语法看起来有些令人生畏，但是如果我们从头到尾地通读，它是非常直观的.请注意以下条款:

The syntax at first looks a little daunting, but if we read through from top to bottom, it is quite intuitive. Note the following clauses:

•MERGE(第1行):如前所述，这是Oracle中的第4条DML语句.我们可能希望直接添加的任何提示都跟在此关键字之后(即MERGE/* + HINT */)；

•MERGE (line 1): as stated previously, this is now the 4th DML statement in Oracle. Any hints we might wish to add directly follow this keyword (i.e. MERGE /*+ HINT */);

•INTO(第2行):这是我们为MERGE指定目标的方式.目标必须是表或可更新视图(此处不能使用嵌入式视图)；

•INTO (line 2): this is how we specify the target for the MERGE. The target must be either a table or an updateable view (an in-line view cannot be used here);

•USING(第3行):USING子句表示MERGE的源数据集.它可以是单个表(如我们的示例中所示)或嵌入式视图；

•USING (line 3): the USING clause represents the source dataset for the MERGE. This can be a single table (as in our example) or an in-line view;

•ON()(第4行):ON子句是我们在源数据集和目标表之间提供联接的位置.请注意，连接条件必须用括号括起来；

•ON () (line 4): the ON clause is where we supply the join between the source dataset and target table. Note that the join conditions must be in parentheses;

•匹配时(第5行):当在目标表中已经有匹配的记录(即源数据集和目标数据集之间存在联接)时，此子句指示Oracle如何处理.在这种情况下，我们显然希望进行UPDATE.此子句的限制之一是我们不能更新ON子句中使用的任何列(尽管我们已经不需要匹配它们，但是我们当然不需要).任何试图包含连接列的尝试都会引发一个不直观的无效标识符异常；和

•WHEN MATCHED (line 5): this clause is where we instruct Oracle on what to do when we already have a matching record in the target table (i.e. there is a join between the source and target datasets). We obviously want an UPDATE in this case. One of the restrictions of this clause is that we cannot update any of the columns used in the ON clause (though of course we don't need to as they already match). Any attempt to include a join column will raise an unintuitive invalid identifier exception; and

•未匹配时"(第10行):此子句是我们在其中插入当前不匹配的记录的地方.

•WHEN NOT MATCHED (line 10): this clause is where we INSERT records for which there is no current match.

这篇关于Oracle SQL Update查询需要几天的时间来更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！