oracle 大数据量更新

最新推荐文章于 2023-09-26 11:37:42 发布

二两芝麻

最新推荐文章于 2023-09-26 11:37:42 发布

阅读量4k

点赞数

分类专栏： oracle

oracle 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

批量更新，MERGE语句性能最好，因为它可以多块读，并且可以并行执行，但是缺点就是消耗比较多的UNDO，一旦down机死事物恢复较慢。
ORDER BY ROWID 在 buffer cache 不够大的情况下性能较好好(没Merge快，因为Merge可以多块读，走ROWID只能单块读)。
优点就是可以批量提交。缺点就是不能并行更新。
不 ORDER BY ROWID 在 buffer cache足够大(能确保被更新的表不被page out) 的情况下性能较好。
create table a as select * from dba_objects;
create table b as select * from dba_objects;

insert into b select * from b; --- 直到插入60W数据
SQL> SELECT COUNT(*) FROM B;
  COUNT(*)
----------
    616864
SQL>  SELECT SUM(BYTES)/1024/1024 "SIZE(MB)" FROM DBA_SEGMENTS WHERE SEGMENT_NAME='B';
  SIZE(MB)
----------
        72
create index idx_a on a(object_name,object_id);
create index idx_b on b(object_id);

比如要执行这个update b set b.object_name=(select a.object_name from a where a.object_id=b.object_id);
可以用MERGE代替
-------------------------MERGE版本,使用MERGE一定要确保MERGE into 的表走全表扫描----------------

alter session set db_file_multiblock_read_count=128;
如果要更新的表很大，alter session enable parallel dml;
                    alter session set workarea_size_policy=manual;
                    alter session set sort_area_size=xxx;
                    alter session set hash_area_size=xxx;

merge /*+ USE_HASH(C,H) FULL(C) */ into b c
using (select /*+INDEX(A) USE_HASH(A) */ a.object_name, a.object_id
         from a
        where a.object_id in (select /*+ use_hash(b) index(b) */ object_id from b)) h
on (c.object_id = h.object_id)
when matched then
  update set c.object_name = h.object_name;
select * from table(dbms_xplan.display);

也可以写PL/SQL
-------------------------PL/SQL版本---------------------------------

DECLARE
  CURSOR CUR_B IS
    SELECT a.object_id, a.object_name, b.ROWID ROW_ID
      FROM A, B
     WHERE A.object_id = B.object_id
     ORDER BY B.ROWID;
  V_COUNTER NUMBER;
BEGIN
  V_COUNTER := 0;
  FOR ROW_B IN CUR_B LOOP
    UPDATE b SET object_name = ROW_B.object_name WHERE ROWID = ROW_B.ROW_ID;
    V_COUNTER := V_COUNTER + 1;
    IF (V_COUNTER >= 10000) THEN
      COMMIT;
      dbms_output.put_line('Updated: ' ||V_COUNTER || ' lines.');
      V_COUNTER := 0;
    END IF;
  END LOOP;
  COMMIT;
END;
/

下面这个版本是批量处理的版本
------------------------批量处理版本--------------------------------

declare
  maxrows      number default 100000;
  row_id_table dbms_sql.urowid_table;
  --currcount_table dbms_sql.number_Table;
  object_name_table dbms_sql.varchar2_Table;
  cursor cur_b is
    SELECT /*+ index(a) use_hash(a,b) index(b) */
     a.object_name, b.ROWID ROW_ID
      FROM A, B
     WHERE A.object_id = B.object_id
     ORDER BY B.ROWID;
  v_counter number;
begin
  v_counter := 0;
  open cur_b;
  loop
    EXIT WHEN cur_b%NOTFOUND;
    FETCH cur_b bulk collect
      into object_name_table, row_id_table limit maxrows;
    forall i in 1 .. row_id_table.count
      update b
         set object_name = object_name_table(i)
       where rowid = row_id_table(i);
    commit;
  end loop;
end;
/

关于ORDER BY ROWID提升速度的验证
-------------------Buffer cache 不够大---------------------------------------

buffer cache 40Mb，B表有72Mb 所以不够存放下 B
keep pool 52Mb--keep idx_a,idx_b
SQL> alter system flush buffer_cache;
系统已更改。
已用时间:  00: 00: 00.00
SQL> merge into b c
  2  using (select a.object_name, a.object_id
  3           from a
  4          where a.object_id in (select object_id from b)) h
  5  on (c.object_id = h.object_id)
  6  when matched then
  7    update set c.object_name = h.object_name;

616851 行已合并。
已用时间:  00: 00: 12.51
SQL>  alter system flush buffer_cache;
系统已更改。
已用时间:  00: 00: 00.00
SQL> declare
  2    maxrows      number default 100000;
  3    row_id_table dbms_sql.urowid_table;
  4    --currcount_table dbms_sql.number_Table;
  5    object_name_table dbms_sql.varchar2_Table;
  6    cursor cur_b is
  7      SELECT /*+ index(a) use_hash(a,b) index(b) */
  8       a.object_name, b.ROWID ROW_ID
  9        FROM A, B
 10       WHERE A.object_id = B.object_id
 11       ORDER BY B.ROWID;   ------有ORDER BY ROWID
 12    v_counter number;
 13  begin
 14    v_counter := 0;
 15    open cur_b;
 16    loop
 17      EXIT WHEN cur_b%NOTFOUND;
 18      FETCH cur_b bulk collect
 19        into object_name_table, row_id_table limit maxrows;
 20      forall i in 1 .. row_id_table.count
 21        update b
 22           set object_name = object_name_table(i)
 23         where rowid = row_id_table(i);
 24      commit;
 25    end loop;
 26  end;
 27  /
PL/SQL 过程已成功完成。
已用时间:  00: 00: 31.71
SQL> alter system flush buffer_cache;
系统已更改。
已用时间:  00: 00: 01.87
SQL> declare
  2    maxrows      number default 100000;
  3    row_id_table dbms_sql.urowid_table;
  4    --currcount_table dbms_sql.number_Table;
  5    object_name_table dbms_sql.varchar2_Table;
  6    cursor cur_b is
  7      SELECT /*+ index(a) use_hash(a,b) index(b) */
  8       a.object_name, b.ROWID ROW_ID
  9        FROM A, B
 10       WHERE A.object_id = B.object_id;
 11    v_counter number;
 12  begin
 13    v_counter := 0;
 14    open cur_b;
 15    loop
 16      EXIT WHEN cur_b%NOTFOUND;
 17      FETCH cur_b bulk collect
 18        into object_name_table, row_id_table limit maxrows;
 19      forall i in 1 .. row_id_table.count
 20        update b
 21           set object_name = object_name_table(i)
 22         where rowid = row_id_table(i);
 23      commit;
 24    end loop;
 25  end;
 26  /
PL/SQL 过程已成功完成。
已用时间:  00: 01: 25.64

MERGE只需要13秒，ORDER BY ROWID 的PL/SQL 需要32秒，而没ORDER BY ROWID 的PL/SQL 需要1分26秒
如果buffer cache不够大(不能容纳下A,B)，不order by rowid 要花1分25秒，order by rowid只花了32秒
可见，ORDER BY ROWID 在BUFFER CACHE不够大的情况下，对于速度的提升是非常明显的，因为buffer cache不够大，block可能经常被page out。
order by rowid 会连续更新临近的block,这样就确保读入的block尽可能的不被page out。
为什么Merge 比用ROWID 去更新快呢？因为MERGE可以多块读，做MERGE的时候设置参数db_file_multiblock_read_count=128
根据ROWID去更新，只能一次读一个block
---------------------BUFFER CACHE 足够大，多次执行，取运行最快的时间---------------------------------------

SQL> alter system set db_keep_cache_size=150m;
系统已更改。
SQL> alter table a storage(buffer_pool keep);
表已更改。
SQL> alter table b storage(buffer_pool keep);
表已更改。
SQL> select /*+ full(a) */ count(*) from a;
  COUNT(*)
----------
     50526
已用时间:  00: 00: 00.16
SQL> select /*+ full(b) */ count(*) from b;
  COUNT(*)
----------
    616864
已用时间:  00: 00: 01.08
SQL> SELECT o.OWNER,o.object_type,o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS
  2       FROM DBA_OBJECTS o, V$BH bh
  3      WHERE o.DATA_OBJECT_ID = bh.OBJD
  4        AND o.owner='SCOTT'
  5      GROUP BY o.owner,o.object_type, o.OBJECT_NAME
  6      ORDER BY COUNT(*)desc ,2 ;
OWNER                          OBJECT_TYPE         OBJECT_NAME                    NUMBER_OF_BLOCKS
------------------------------ ------------------- ------------------------------ ----------------
SCOTT                          TABLE               B                                          8467
SCOTT                          INDEX               IDX_B                                      1394
SCOTT                          TABLE               A                                           695
SCOTT                          INDEX               IDX_A                                       288
SQL> declare
  2    maxrows      number default 100000;
  3    row_id_table dbms_sql.urowid_table;
  4    --currcount_table dbms_sql.number_Table;
  5    object_name_table dbms_sql.varchar2_Table;
  6    cursor cur_b is
  7      SELECT /*+ index(a) use_hash(a,b) index(b) */
  8       a.object_name, b.ROWID ROW_ID
  9        FROM A, B
 10       WHERE A.object_id = B.object_id
 11       ORDER BY B.ROWID;
 12    v_counter number;
 13  begin
 14    v_counter := 0;
 15    open cur_b;
 16    loop
 17      EXIT WHEN cur_b%NOTFOUND;
 18      FETCH cur_b bulk collect
 19        into object_name_table, row_id_table limit maxrows;
 20      forall i in 1 .. row_id_table.count
 21        update b
 22           set object_name = object_name_table(i)
 23         where rowid = row_id_table(i);
 24      commit;
 25    end loop;
 26  end;
 27  /
PL/SQL 过程已成功完成。
已用时间:  00: 00: 11.83
SQL> declare
  2    maxrows      number default 100000;
  3    row_id_table dbms_sql.urowid_table;
  4    --currcount_table dbms_sql.number_Table;
  5    object_name_table dbms_sql.varchar2_Table;
  6    cursor cur_b is
  7      SELECT /*+ index(a) use_hash(a,b) index(b) */
  8       a.object_name, b.ROWID ROW_ID
  9        FROM A, B
 10       WHERE A.object_id = B.object_id;
 11    v_counter number;
 12  begin
 13    v_counter := 0;
 14    open cur_b;
 15    loop
 16      EXIT WHEN cur_b%NOTFOUND;
 17      FETCH cur_b bulk collect
 18        into object_name_table, row_id_table limit maxrows;
 19      forall i in 1 .. row_id_table.count
 20        update b
 21           set object_name = object_name_table(i)
 22         where rowid = row_id_table(i);
 23      commit;
 24    end loop;
 25  end;
 26  /
PL/SQL 过程已成功完成。
已用时间:  00: 00: 09.71
SQL> merge into b c
  2  using (select a.object_name, a.object_id
  3           from a
  4          where a.object_id in (select object_id from b)) h
  5  on (c.object_id = h.object_id)
  6  when matched then
  7    update set c.object_name = h.object_name;
616851 行已合并。
已用时间:  00: 00: 08.54

ORDER BY ROWID 的PL/SQL 用了11秒，没有ORDER BY ROWID的PL/SQL用了9秒，而Merge最快，只花了8秒多。
(反复测试，以最快时间为准，添加了3个logfile group 每组500Mb,减少logfile对测试的影响)。
由此可见，如果buffer cache够大，不order by rowid 反比order by rowid更快(因为少了排序)

from https://blog.csdn.net/u013820054/article/details/51781145