PL/SQL rownum 更新

Question

我正在开发一个包含几个表的数据库。它们是一个

      districts table
      PK district_id

      student_data table
      PK study_id
      FK district_id

      ga_data table
      PK study_id
      district_id

ga_data 表是我添加的数据。student_data 表和 ga_data 都有 130 万条记录。两个表之间的 Study_id 为 1 比 1，但 ga_data.district_id 为 NULL，需要更新。我在使用以下 PL/SQL 时遇到问题：

update ga_data
set district_id = (select district_id from student_data
where student_data.study_id = ga_data.study_id)
where ga_data.district_id is null and rownum < 100;

我需要增量地执行它，这就是我需要 rownum 的原因。但我使用得正确吗？运行查询多次后，它只更新了 130 万条记录中的大约 8,000 条记录（应该是大约 110 万条更新，因为学生数据中的一些 District_id 为空）。谢谢！

Answer 1

ROWNUM 只是在前 n 行之后截断查询。 STUDENT_DATA 中有一些行的 DISTRICT_ID 为 NULL。因此，在多次运行之后，您的查询很可能会陷入困境，返回相同的 100 条 QA_DATA 记录，所有这些记录都与那些讨厌的 STUDENT_DATA 行之一匹配。

因此，您需要某种机制来确保您按照自己的方式逐步完成 QA_DATA 表。标志列是一种解决方案。另一种方法是对查询进行分区，使其命中一组不同的 STUDENT_ID。

目前尚不清楚为什么必须以 100 个为批次执行此操作，但也许最简单的方法是使用 BULK PROCESSING（至少在 Oracle 中：此 PL/SQL 语法在 MySQL 中不起作用）。

以下是一些测试数据：

SQL> select district_id, count(*)
  2  from student_data
  3  group by district_id
  4  /

DISTRICT_ID   COUNT(*)
----------- ----------
   7369        192
   7499        190
   7521        192
   7566        190
   7654        192
   7698        191
   7782        191
   7788        191
   7839        191
   7844        192
   7876        191
   7900        192
   7902        191
   7934        192
   8060        190
   8061        193
   8083        190
   8084        193
   8085        190
   8100        193
   8101        190
               183

22 rows selected.

SQL> select district_id, count(*)
  2  from qa_data
  3  group by district_id
  4  /

DISTRICT_ID   COUNT(*)
----------- ----------
                  4200

SQL>

该匿名块使用 Bulk 处理 LIMIT 子句将结果集批处理为 100 行的块。

SQL> declare
  2      type qa_nt is table of qa_data%rowtype;
  3      qa_recs qa_nt;
  4
  5      cursor c_qa is
  6          select qa.student_id
  7                 , s.district_id
  8          from qa_data qa
  9                  join student_data s
 10                      on (s.student_id = qa.student_id);
 11  begin
 12      open c_qa;
 13
 14      loop
 15          fetch c_qa bulk collect into qa_recs limit 100;
 16          exit when qa_recs.count() = 0;
 17
 18          for i in qa_recs.first()..qa_recs.last()
 19          loop
 20              update qa_data qt
 21                  set qt.district_id = qa_recs(i).district_id
 22                  where qt.student_id = qa_recs(i).student_id;
 23          end loop;
 24
 25      end loop;
 26  end;
 27  /

PL/SQL procedure successfully completed.

SQL>

请注意，此构造允许我们在发布更新之前对所选行进行附加处理。如果我们需要以编程方式应用复杂的修复，这会很方便。

如您所见，QA_DATA 中的数据现在与 STUDENT_DATA 中的数据匹配

SQL> select district_id, count(*)
  2  from qa_data
  3  group by district_id
  4  /

DISTRICT_ID   COUNT(*)
----------- ----------
   7369        192
   7499        190
   7521        192
   7566        190
   7654        192
   7698        191
   7782        191
   7788        191
   7839        191
   7844        192
   7876        191
   7900        192
   7902        191
   7934        192
   8060        190
   8061        193
   8083        190
   8084        193
   8085        190
   8100        193
   8101        190
               183

22 rows selected.

SQL>

Answer 2

一次只更新 100 行是一个奇怪的要求。这是为什么？

无论如何，由于student_data 中的district_id 可能为空，因此您可能会一遍又一遍地更新相同的100 行。

如果您扩展查询以确保存在非空的 District_id，您可能会到达您想要的位置：

update ga_data
set district_id = (
  select district_id 
  from student_data
  where student_data.study_id = ga_data.study_id
)
where ga_data.district_id is null 
and exists (
  select 1
  from student_data
  where student_data.study_id = ga_data.study_id
  and district_id is not null
)
and rownum < 100;

Answer 3

如果这是一次性转换，您应该考虑一种完全不同的方法。重新创建该表作为两个表的联接。我保证，当您意识到它与各种有趣的一次 100 行更新相比有多快时，您会大笑起来。

create table new_table as
   select study_id
         ,s.district_id
         ,g.the_remaining_columns_in_ga_data
    from student_data s
    join ga_data      g using(study_id);

   create indexes, constraints etc 
   drop table ga_data;
   alter table new_table rename to ga_data;

或者，如果它不是一次性转换，或者您无法重新创建/删除表，或者您只是想在数据加载上多花几个小时：

merge
 into ga_data      g
using student_data s
   on (g.study_id  = s.study_id)
when matched then
   update
      set g.district_id = s.district_id;

最后一条语句也可以重写为可更新视图，但我个人从不使用它们。

在运行合并之前删除/禁用 ga_data.district_id 上的索引/约束并在之后重新创建它们将提高性能。