sql - 从多个源有效地更新表

Question

我正在努力改进 Oracle 中现有 ETL 层的一部分。

1. 一个文件被加载到一个临时表中。
2. 执行许多 MERGE 语句来解析代理键。
3. 应用了其他一些业务逻辑(需要那些代理键)。
4. 结果已合并进入一个表(同时具有代理键和业务逻辑结果)

我想改进的是第 2 步，似乎不太理想将其分为几个步骤。

MERGE INTO temp t
USING dimension_1 d1 ON (d1.natural_key = t.d1_natural_key)
WHEN MATCHED THEN UPDATE t.d1_id = d1.id

MERGE INTO temp t
USING dimension_2 d2 ON (d2.natural_key = t.d2_natural_key)
WHEN MATCHED THEN UPDATE t.d2_id = d2.id

MERGE INTO temp t
USING dimension_3 d3 ON (d3.natural_key = t.d3_natural_key)
WHEN MATCHED THEN UPDATE t.d3_id = d3.id

如果我在 SQL Server 中编写此代码，我会执行如下操作:

UPDATE
    t
SET
    d1_id = COALESCE(d1.id, -1),
    d2_id = COALESCE(d2.id, -1),
    d3_id = COALESCE(d3.id, -1)
FROM
    temp  t
LEFT JOIN
    dimension_1   d1
        ON d1.natural_key = t.d1_natural_key
LEFT JOIN
    dimension_2   d2
        ON d2.natural_key = t.d2_natural_key
LEFT JOIN
    dimension_3   d3
        ON d3.natural_key = t.d3_natural_key

在我的一生中，我无法在 Oracle 中找到一个看似合理的选项。我能解决的最好的方法是使用 UPDATE (而我周围的每个人都在尖叫我“必须”使用 MERGE) 和相关的子查询；像...

UPDATE
    temp t
SET
    d1_id = COALESCE((SELECT id FROM dimension_1 d1 WHERE d1.natural_key = t.d1_natural_key), -1),
    d2_id = COALESCE((SELECT id FROM dimension_2 d2 WHERE d2.natural_key = t.d2_natural_key), -1),
    d3_id = COALESCE((SELECT id FROM dimension_3 d3 WHERE d3.natural_key = t.d3_natural_key), -1)

有没有更好的选择？还是相关子查询方法在 Oracle 中的实际性能？

Answer 1

我认为您的 SQL Server 更新等效于:

UPDATE
    temp t1
SET
    (d1_id, d2_id, d3_id) = (
SELECT
    COALESCE(d1.id, -1),
    COALESCE(d2.id, -1),
    COALESCE(d3.id, -1)
FROM
    temp  t2
LEFT JOIN
    dimension_1   d1
        ON d1.natural_key = t2.d1_natural_key
LEFT JOIN
    dimension_2   d2
        ON d2.natural_key = t2.d2_natural_key
LEFT JOIN
    dimension_3   d3
        ON d3.natural_key = t2.d3_natural_key
WHERE
    t2.id = t1.id 
)

它仍然是一个相关的更新；加入发生在子查询中，因为 Oracle 不允许您加入作为更新本身的一部分。通常您不需要(或不想)在子查询中再次引用目标外表，但您需要一些东西来针对此处进行外部联接。

您还可以将左连接方法与合并结合起来，将本质上相同的子查询放入 using 子句中:

MERGE INTO temp t
USING (
  SELECT t.id,
    COALESCE(d1.id, -1) AS d1_id,
    COALESCE(d2.id, -1) AS d2_id,
    COALESCE(d3.id, -1) AS d3_id
  FROM
    temp  t
  LEFT JOIN
    dimension_1   d1
      ON d1.natural_key = t.d1_natural_key
  LEFT JOIN
    dimension_2   d2
      ON d2.natural_key = t.d2_natural_key
  LEFT JOIN
    dimension_3   d3
      ON d3.natural_key = t.d3_natural_key
) d
ON (d.id = t.id)
WHEN MATCHED THEN UPDATE SET
  t.d1_id = d.d1_id,
  t.d2_id = d.d2_id,
  t.d3_id = d.d3_id

虽然在这种情况下，我没有看到使用合并而不是更新有任何真正的好处。

两者都将覆盖您的三个 ID 列中的任何现有值，但听起来您并不期望有任何值。