我需要将数据从 2 个 tables（Table A 和 Table B）传输到一个新的 table。我正在使用查询来使用 ID 列连接 A 和 B tables。 Table A 和 B 是没有任何索引的存档 tables。 （百万条记录） Table X 和 Y 是具有良好索引的 A 和 B 的副本。 （几千条记录）
下面是我的项目的代码。
with data as
(
SELECT a.*, b.* FROM A_archive a
join B_archive b where a.transaction_id = b.transaction_id
UNION
SELECT x.*, y.* FROM X x
join Y y where x.transaction_id = y.transaction_id
)
INSERT INTO
Another_Table
(
columns
)
select * from data
On Conflict(transaction_id)
do udpate ...上面整个东西是在生产环境中运行的，有近 1.4 亿条记录。由于这个生产数据库需要将近 10 个小时来处理数据并且失败。
我还在 AWS 中有一个分布式作业调度程序，用于在函数内安排此查询并每 5 小时检索一次最新记录。归档 tables store 已关闭发票数据。 Pega UI 将使用此 table 来检索有关已关闭发票的数据并显示给客户。
请建议一些性能更高的东西。
回答1
UNION 删除重复的行。在大型未索引 tables 上，这是一项昂贵的操作。如果您不需要重复数据删除，请尝试 UNION ALL。它将节省重复数据删除所需的大量数据混洗和比较。
如果您的档案 tables 没有索引，您的 JOIN 操作将非常低效。至少索引您在 ON 子句中使用的 transaction_id 列。
你没有说你想对生成的 table 做什么。在许多情况下，您可以使用 VIEW 而不是 table 来实现您的目的。 VIEW 删除了创建派生 table 的工作。实际上，它将工作推迟到使用派生结构的 SELECT 操作的时间。如果您的 SELECT 操作具有高度选择性的 WHERE 子句，那么节省的费用可能会非常惊人。为了使它正常工作，您可能需要在您的档案 tables 上放置适当的索引。
当您可以枚举所需的列时，您可以使用 SELECT *。这肯定会在您的结果中添加一个冗余列：它会生成两个 transaction_id 副本。它还可能生成其他冗余或未使用的数据。除非您知道自己需要它，否则请始终避免在生产软件中使用 SELECT *。
请记住这一点：SQL 是声明性的，而不是过程性的。您声明（描述）您需要的结果，并让服务器找出获得它的最佳方式。在像您的 table 组合这样的情况下，视图让服务器为您完成这项工作。它将尽可能使用您提供的索引。
回答2
那个 UNION 一定很昂贵，它几乎在后台构建了一个 temp-table ，其中包含所有 A-B + X-Y 记录，对其进行排序（在所有字段上），然后删除任何双精度数。如果您说涉及 1 亿条记录，那么进行的大量排序很可能涉及换出到磁盘。
请记住，只有在存在预期重复项时才需要执行此操作
- 在
A和B之间的JOIN的结果中 - 在
X和Y之间的JOIN的结果中 - 在上述两者的综合结果中
如果这些都不是预期的，则使用 UNION ALL 事实上，在这种情况下，为什么不对 A-B 进行 1 个 INSERT 操作，而对 X-Y 进行另一个 INSERT 操作？按照描述，我会说 X-Y 中的任何内容都应该推翻 A-B 中的任何内容，对吗？
此外，正如 O.Jones 所提到的，无论是否归档 tables，它们至少应该在您正在 JOIN 处理的 transaction_id 字段上附带一个（最好是聚集的）索引。 （对于 Another_Table 顺便说一句）
综上所述，在 1 个事务中处理 1 亿条记录需要一些时间，只是需要移动大量数据。但是 10h 确实听起来有点过分。