基于远程表更新/插入表的最佳方法

发布于 2024-08-31 09:12:27 字数 1181 浏览 2 评论 0原文

我在 Oracle 10g 数据库中有两个非常大的企业表。一张表保存另一张表的历史信息。问题是，我已经到了记录太多以至于我的插入更新花费太长时间并且我的会话被州长杀死的地步。

这是我的更新过程的伪代码：

sqlsel := 'SELECT col1, col2, col3, col4 sysdate  
        FROM table2@remote_location dpi
         WHERE (col1, col2, col3) IN
               (
                SELECT col1, col2, col3
                  FROM table2@remote_location 
                 MINUS
                SELECT DISTINCT col1, col2, col3
                  FROM table1 mpc
                 WHERE facility = '''||load_facility||'''
               )';

EXECUTE IMMEDIATE sqlsel BULK COLLECT
                 INTO table1;

我尝试过 MERGE 语句：

MERGE INTO table1 t1
USING (
  SELECT col1, col2, col3  FROM table2@remote_location 
  ) t2
ON (
t1.col1 = t2.col1 AND
t1.col2 = t2.col2 AND
t1.col3 = t2.col3 
)

WHEN NOT MATCHED THEN
  INSERT (t1.col1, t1.col2, t1.col3, t1.update_dttm  )
  VALUES (t2.col1, t2.col2, t2.col3, sysdate  )

但似乎有一个在 Oracle 10.2.0.4 之前的版本上，使用远程数据库进行合并时，已确认合并语句上的错误。获得企业升级的机会很小，因此有没有办法进一步优化我的第一个查询或以其他方式编写它以使其运行最佳性能？

谢谢。

原文

I have two very large enterprise tables in an Oracle 10g database. One table keeps the historical information of the other table. The problem is, I'm getting to the point where the records are just too many that my insert update is taking too long and my session is getting killed by the governor.

Here's a pseudocode of my update process:

sqlsel := 'SELECT col1, col2, col3, col4 sysdate  
        FROM table2@remote_location dpi
         WHERE (col1, col2, col3) IN
               (
                SELECT col1, col2, col3
                  FROM table2@remote_location 
                 MINUS
                SELECT DISTINCT col1, col2, col3
                  FROM table1 mpc
                 WHERE facility = '''||load_facility||'''
               )';

EXECUTE IMMEDIATE sqlsel BULK COLLECT
                 INTO table1;

I've tried the MERGE statement:

MERGE INTO table1 t1
USING (
  SELECT col1, col2, col3  FROM table2@remote_location 
  ) t2
ON (
t1.col1 = t2.col1 AND
t1.col2 = t2.col2 AND
t1.col3 = t2.col3 
)

WHEN NOT MATCHED THEN
  INSERT (t1.col1, t1.col2, t1.col3, t1.update_dttm  )
  VALUES (t2.col1, t2.col2, t2.col3, sysdate  )

But there seems to be a confirmed bug on versions prior to Oracle 10.2.0.4 on the merge statement when doing a merge using a remote database. The chance of getting an enterprise upgrade is slim so is there a way to further optimize my first query or write it in another way to have it run best performance wise?

Thanks.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

月亮邮递员 2024-09-07 09:12:27

您是否查看过物化视图来执行同步？可以在询问 Anantha 找到相关内容。 Oracle 白皮书也不错。

回复收藏 0 原文

ゞ花落谁相伴 2024-09-07 09:12:27

如果 table2@remote 中有重复的 col1/col2/col3 条目，那么您的查询将返回它们。如果不需要它们，那么你可以做一个

SELECT col1, col2, col3, sysdate  
FROM (
     SELECT col1, col2, col3
     FROM table2@remote_location 
     MINUS
     SELECT col1, col2, col3
     FROM table1 mpc
     WHERE facility = '''||load_facility||'''
     )

你也可以摆脱 DISTINCT 。 MINUS 是集合运算，因此没有必要。

If there are duplicate col1/col2/col3 entries in table2@remote, then your query will return them. if they are not needed, then you could do a

SELECT col1, col2, col3, sysdate  
FROM (
     SELECT col1, col2, col3
     FROM table2@remote_location 
     MINUS
     SELECT col1, col2, col3
     FROM table1 mpc
     WHERE facility = '''||load_facility||'''
     )

You can get rid of the DISTINCT too. MINUS is a set operation and so it is unnecessary.

回复收藏 0 原文

~没有更多了~