oracle大数据量dml操作思路

思路1:
  1：根据ROWID分片,排序。
  2：for all 批量处理
  3：回表删除
  4：在业务低峰时操作
可以参考下面脚本：

declare  
   cursor mycursor is SELECT  ROWID FROM TEST WHERE  col1=XXXX  order by rowid;
   type rowid_table_type is  table  of rowid index by pls_integer;
   v_rowid   rowid_table_type;
BEGIN
   open mycursor;
   loop
     fetch   mycursor bulk collect into v_rowid  limit 5000;
     exit when v_rowid.count=0;
     forall i in v_rowid.first..v_rowid.last
        delete from test  where rowid=v_rowid(i);
     commit;
   end loop;
   close mycursor;
END;

总结：这种方法的缺点是排序有可能会消耗太多临时表空间，特别是超过1000万的表。还有一种方式，先根据Rowid分片。
分片处理方法：

SELECT dbms_rowid.rowid_create(1, 71294, file_id, MIN(block_id), 0),
       dbms_rowid.rowid_create(1, 71294, file_id, MAX(block_id + blocks - 1),8192)
  FROM dba_extents
 WHERE segment_name = 'CLIENTINFO_DATA'
 GROUP BY file_id
 ORDER BY file_id;
DBMS_ROWID.ROWID_CREATE语法：
DBMS_ROWID.ROWID_CREATE (
    rowid_type           IN NUMBER,
    object_number    IN NUMBER,
    relative_fno          IN NUMBER,
    block_number     IN NUMBER,
    row_number       IN NUMBER)

参数：
row