sql - 拥有超过 1000 万条记录的快速更新数据库

Question

我是 SQL 的新手，想知道是否有人可以帮助我。

我有一个大约有 1000 万行的数据库。

我需要制作一个脚本来查找具有一些 NULL 字段的记录，然后将其更新为某个值。

我在执行一个简单的更新语句时遇到的问题是它会耗尽回滚空间。

我读到我需要使用 BULK COLLECT AND FETCH。

我的想法是一次获取 10,000 条记录，更新、提交并继续获取。

我尝试在 Google 上寻找示例，但我还没有找到任何东西。

有什么帮助吗？

谢谢!!

这是我目前所拥有的:

DECLARE
    CURSOR rec_cur IS
    SELECT DATE_ORIGIN 
    FROM MAIN_TBL WHERE DATE_ORIGIN IS NULL;

    TYPE date_tab_t IS TABLE OF DATE;

    date_tab date_tab_t;

BEGIN
    OPEN rec_cur;
    LOOP
        FETCH rec_cur BULK COLLECT INTO date_tab LIMIT 1000;
        EXIT WHEN date_tab.COUNT() = 0;

        FORALL i IN 1 .. date_tab.COUNT
            UPDATE MAIN_TBL SET DATE_ORIGIN  = '23-JAN-2012' 
            WHERE DATE_ORIGIN IS NULL;

    END LOOP;
    CLOSE rec_cur;
END;

Answer 1

我想我明白你想做什么。关于下面的代码和您的代码之间的区别，我想指出很多要点。

1. 您的 forall 循环不会使用索引。通过使用 rowid 更新您的表格，这很容易解决。
2. 通过在每次 forall 之后提交，您可以减少所需的撤消次数；但如果出现问题，回滚会更加困难。尽管从逻辑上讲，您的查询可以在中间轻松地重新开始，而不会损害您的目标。
3. rowids 小，一次至少收集25k；如果不是 100k。
4. 您不能在 Oracle 中索引空值。 stackoverflow 上有很多关于此的问题，您需要更多信息。像 nvl(date_origin,'x') 这样的功能索引作为一个松散的例子会提高你选择数据的速度。这也意味着您实际上不必使用表格本身。您只需从索引中选择。
5. 您的日期数据类型似乎是一个字符串。我保留了这个，但这并不明智。
6. 如果您能让某人增加您的撤消表空间大小，那么直接更新会更快。

假设根据您的评论 date_origin 是一个日期，那么索引应该是这样的:

nvl(date_origin,to_date('absolute_minimum_date_in_Oracle_as_a_string','yyyymmdd'))

我目前无法访问数据库，但要找出 amdiOaas，请运行以下查询:

select to_date('0001','yyyy') from dual;

它应该会为您引发一个有用的错误。

---

PL/SQL Developer 中的工作示例。

 create table main_tbl as
  select cast( null as date ) as date_origin
    from all_objects
         ;

create index i_main_tbl
   on main_tbl ( nvl( to_date(date_origin,'yyyy-mm-dd')
                    , to_date('0001-01-01' ,'yyyy-mm-dd') )
                )
      ;

declare

   cursor c_rec is
    select rowid
      from main_tbl
     where nvl(date_origin,to_date('0001-01-01','yyyy-mm-dd')) 
               = to_date('0001-01-01','yyyy-mm-dd')
          ;

   type t__rec is table of rowid index by binary_integer;
   t_rec t__rec;

begin

   open c_rec;
   loop

      fetch c_rec bulk collect into t_rec limit 50000;

      exit when t_rec.count = 0;

      forall i in t_rec.first .. t_rec.last
         update main_tbl
            set date_origin = to_date('23-JAN-2012','DD-MON-YYYY')
          where rowid = t_rec(i)
                ;
         commit ;

   end loop;
   close c_rec;
end;
/