ORACLE/SQL - 需要优化“合并”式脚本的帮助

Question

我们有一个“合并”脚本，用于为客户分配代码。目前，该脚本通过查看暂存表中的客户并分配未使用的代码来工作。这些代码被标记为已使用，并将带有代码的暂存记录加载到生产表中。暂存表被清除，一切都很好。

不幸的是，我们现在正在处理更大的数据集（包括客户和代码），而且该过程运行时间太长了。我希望这里的精英社区可以查看这里的代码，并提供改进或另一种解决问题的方法。

谢谢！

编辑 - 忘记提到在这个检查的部分的原因是暂存表是“活动的”，在脚本运行期间可能会有记录进入其中。

whenever sqlerror exit 1

-- stagingTable: TAB_000000003134
-- codeTable: TAB_000000003135
-- masterTable: TAB_000000003133

-- dedupe staging table
delete from TAB_000000003134 a
where ROWID > (
  select min(rowid)
  from TAB_000000003134 b
  where a.cust_id = b.cust_id
  );
commit;

delete from TAB_000000003134
where cust_id is null;
commit;


-- set row num on staging table
update TAB_000000003134
set row_num = rownum;
commit;

-- reset row nums on code table
update TAB_000000003135
set row_num = NULL;
commit;

-- assign row nums to codes
update TAB_000000003135
set row_num = rownum
where dateassigned is null
and active = 1;
commit;

-- attach codes to staging table
update TAB_000000003134 d
set (CODE1, CODE2) =
(
  select CODE1, CODE2
  from TAB_000000003135 c
  where d.row_num = c.row_num
);
commit;

-- mark used codes compared to template
update TAB_000000003135 c
set dateassigned = sysdate, assignedto = (select cust_id from TAB_000000003134 d where c.CODE1 = d.CODE1)
where exists (select 'x' from TAB_000000003134 d where c.CODE1 = d.CODE1);
commit;

-- clear and copy data to master
truncate table TAB_000000003133;
insert into TAB_000000003133 (
        <custmomer fields>, code1, code2, TIMESTAMP_
        )
select <custmomer fields>, CODE1, CODE2,SYSDATE
from TAB_000000003134;
commit;

-- remove any staging records with code numbers
delete from TAB_000000003134
where CODE1 is not NULL;
commit;

quit

- dscl

---

在原帖中添加了一些额外的细节。 - dscl

Answer 1

• 尽量合并语句。例如，通过在第一个删除语句中简单地添加"or cust_id is null"来合并前两个删除语句。这将确实减少读取的次数，并且还可能显著减少写入的数据量。（Oracle写入块而不是行，所以即使这两个语句处理不同的行，它们可能会重新写入相同的块。）
• 将整个表插入到另一个表中可能比更新每一行更快。Oracle为了维护并发性和一致性而进行了许多额外的工作来更新和删除。而将值更新为NULL尤其昂贵，请参考update x set y = null takes a long time以获取更多详细信息。您可以使用直接路径插入来避免（几乎所有）UNDO和REDO：确保表处于NOLOGGING模式下（或者数据库处于NOARCHIVELOG模式下），并使用APPEND提示进行插入。
• 用MERGE替换UPDATE。UPDATE只能使用嵌套循环，而MERGE还可以使用哈希连接。如果要更新大量数据，MERGE可能会更快。而且如果表用于SET和EXISTS，MERGE不必读取两次表。（虽然创建一个新表也可能更快。）
• 在TAB_000000003133插入时使用/*+ APPEND */。如果要截断表，我假设您不需要数据的某个时间点恢复功能，所以最好直接将其插入到数据文件中，跳过所有开销。
• 使用并行处理（如果尚未使用）。调优时可能会产生副作用和数十个因素需要考虑，但不要被这些吓到。如果处理大量数据，迟早你将需要使用并行处理来充分发挥硬件的潜力。
• 使用更好的命名。这些建议更具主观性，但在我看来，使用良好的命名非常重要。尽管在某种程度上它都是0和1，许多程序员认为晦涩的代码很酷，但你希望人们能够理解并关心你的数据。人们对TAB_000000003135这样的名称不那么关注，而对类似TAB_CUSTOMER_CODES的名称更感兴趣。学习起来更困难，人们不太可能修改它，因为它看起来很复杂，并且人们不太可能看到错误，因为它的目的不够清晰。

- Jon Heller

---

非常感谢这些技巧 - 现在要尝试实现它们了！我的Oracle/SQL技能不如应该的好，哈哈。 - dscl

Answer 2

• 不要在每个语句后都提交。相反，应该在脚本末尾发出一个COMMIT。这不仅仅是为了性能，而是因为在脚本结束之前，数据处于不一致状态。

（事实证明，在Oracle中更少地提交可能会有性能优势，但您的主要关注点应该是维护一致性）

• 您可以考虑使用全局临时表。全局临时表中的数据仅对当前会话可见，因此您可以跳过脚本中的某些重置步骤。

- Cheran Shunmugavel