在Oracle中删除大量数据

这里还有一个 article 关于Oracle中的大规模删除,您可能需要阅读。

CREATE TABLE AS SELECT 使用 NOLOGGING 选项我的意思是:

ALTER TABLE table_to_delete RENAME TO tmp;
CREATE TABLE table_to_delete NOLOGGING AS SELECT .... ;

当然,您必须重新创建没有验证的约束、没有日志的索引、授权等等。。。但是速度非常非常快。

ALTER TABLE table_to_delete RENAME to tmp;
CREATE VIEW table_to_delete AS SELECT * FROM tmp;
-- Until there can be instantly
CREATE TABLE new_table NOLOGGING AS SELECT .... FROM tmp WHERE ...;
<create indexes with nologging>
<create constraints with novalidate>
<create other things...>
-- From here ...
DROP VIEW table_to_delete;
ALTER TABLE new_table RENAME TO table_to_delete;
-- To here, also instantly

• 存储过程可能会失效,但它们将在第二次调用时重新编译。你必须测试它。
• 意味着 重做是生成的。如果您具有DBA角色,请运行 ALTER SYSTEM CHECKPOINT 确保实例崩溃时不会丢失数据。
• 对于 无记录 表空间也必须在 无记录 .

另一个比创建数以百万计的插入更好的选项是:

-- Create table with ids
DELETE FROM table_to_delete
 WHERE ID in (SELECT ID FROM table_with_ids WHERE ROWNUM < 100000);
DELETE FROM table_with_ids WHERE ROWNUM < 100000;
COMMIT;
-- Run this 50 times ;-)

不建议选择PLSQL,因为它可以创建 快照太旧 由于您正在使用打开的游标(循环游标)提交(并关闭事务)并希望继续使用该游标而导致的消息。甲骨文允许这样做,但这不是一个好的做法。

更新:为什么我可以确保最后一个PLSQL块正常工作?因为我相信:

• 然后,使用最后一个断言执行查询 确切地 使用相同的计划,并将以相同的顺序返回行。

在中执行大规模删除时 Oracle ,请确保没有用完 UNDO SEGMENTS .

表演时 DML , 神谕 首先将所有更改写入 REDO 日志(旧数据与新数据一起)。

重做 日志已填充或发生超时, 神谕 log synchronization new 将数据写入数据文件(在您的情况下,将数据文件块标记为空闲),并将旧数据写入 UNDO 表空间(以便它对并发事务保持可见,直到 commit

打开 YOUR事务占用的段被释放。

这意味着如果您删除 5M 数据行,您需要为 all 打开 分段,以便数据可以首先移动到那里( all at once )并仅在提交后删除。

这也意味着并发查询(如果有)将需要从 日志或 打开 执行表扫描时的段。这不是访问数据的最快方式。

这也意味着如果优化器选择 HASH JOIN 对于删除查询(它很可能会这样做),临时表将不适合 HASH_AREA_SIZE (最有可能是这种情况),那么查询将需要 several 扫描大桌子,桌子的一些部分将被移动到 重做 或 打开 .

鉴于以上所述,您可能最好在中删除数据 200,000 分块并提交中间的更改。

因此,首先,您将摆脱上述问题,其次,优化您的应用程序 HASH_JOIN

不过,在您的情况下,我会尝试强制优化器使用 NESTED LOOPS ,我希望你的情况会更快。

要执行此操作,请确保temp表上有一个主键 ID ,并按如下方式重写查询:

DELETE  
FROM   (
       SELECT  /*+ USE_NL(tt, tn) */
               tn.id
       FROM    temp_table tt, table_name tn
       WHERE   tn.id = tt.id
       )

您需要打开主键 temp_table 使此查询正常工作。

DELETE  
FROM   (
       SELECT  /*+ USE_HASH(tn tt) */
               tn.id
       FROM    temp_table tt, table_name tn
       WHERE   tn.id = tt.id
       )

最好像第一个例子中那样一次完成所有工作。但我肯定会先和你的DBA讨论一下,因为他们可能想回收清除后不再使用的块。此外,可能存在从用户角度通常看不到的调度问题。

如果原始SQL需要很长时间,一些并发SQL可能会运行缓慢,因为它们必须使用UNDO在没有未提交更改的情况下重建数据版本。

妥协可能是这样的

FOR i in 1..100 LOOP
  DELETE FROM table_name WHERE id IN (SELECT id FROM temp_table) AND ROWNUM < 100000;
  EXIT WHEN SQL%ROWCOUNT = 0;
  COMMIT;
END LOOP;

您可以根据需要调整ROWNUM。较小的ROWNUM意味着更频繁的提交,并且(可能)在需要应用undo方面减少了对其他会话的影响。然而,根据执行计划,可能会有其他影响,总体上可能需要更多的时间。 从技术上讲,循环的“FOR”部分是不必要的,因为退出将结束循环。但我对无限循环持偏执态度,因为如果它们被卡住了,那么终止会话是一种痛苦。

我建议将其作为单个删除运行。

是否存在要从中删除的表的子表?如果是,请确保这些表中的外键已编制索引。否则,您可能会对删除的每一行的子表进行完整扫描,这可能会导致速度非常慢。

您可能需要一些方法在删除运行时检查其进度。看见 How to check oracle database for long running queries?

正如其他人所建议的,如果你想测试水,你可以输入:rownum<在你的查询结束时有10000。

我曾经在Oracle 7上做过类似的事情,我不得不从数千个表中删除数百万行。对于全面性能,尤其是大型删除(一个表中的行数超过百万),该脚本运行良好。

删除_sql() 在指定的表中查找一批行ID,然后逐批删除它们。例如

exec delete_sql('MSF710', 'select rowid from msf710 s where  (s.equip_no, s.eq_tran_date, s.comp_data, s.rec_710_type, s.seq_710_no) not in  (select c.equip_no, c.eq_tran_date, c.comp_data, c.rec_710_type, c.seq_710_no  from  msf710_sched_comm c)', 500);

上面的示例基于sql语句一次从表MSF170中删除500条记录。

如果您需要从多个表中删除数据,只需包含其他数据即可 exec delete_sql(...) 文件delete-tables.sql中的行

哦,记住将回滚段放回在线,它不在脚本中。

spool delete-tables.log;
connect system/SYSTEM_PASSWORD
alter rollback segment r01 offline;
alter rollback segment r02 offline;
alter rollback segment r03 offline;
alter rollback segment r04 offline;

connect mims_3015/USER_PASSWORD

CREATE OR REPLACE PROCEDURE delete_sql (myTable in VARCHAR2, mySql in VARCHAR2, commit_size in number) is
  i           INTEGER;
  sel_id      INTEGER;
  del_id      INTEGER;
  exec_sel    INTEGER;
  exec_del    INTEGER;
  del_rowid   ROWID;

  start_date  DATE;
  end_date    DATE;
  s_date      VARCHAR2(1000);
  e_date      VARCHAR2(1000);
  tt          FLOAT;
  lrc         integer;


BEGIN
  --dbms_output.put_line('SQL is ' || mySql);
  i := 0;
  start_date:= SYSDATE;
  s_date:=TO_CHAR(start_date,'DD/MM/YY HH24:MI:SS');


  --dbms_output.put_line('Deleting ' || myTable);
  sel_id := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(sel_id,mySql,dbms_sql.v7);
  DBMS_SQL.DEFINE_COLUMN_ROWID(sel_id,1,del_rowid);
  exec_sel := DBMS_SQL.EXECUTE(sel_id);
  del_id := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(del_id,'delete from ' || myTable || ' where rowid = :del_rowid',dbms_sql.v7);
 LOOP
   IF DBMS_SQL.FETCH_ROWS(sel_id) >0 THEN
      DBMS_SQL.COLUMN_VALUE(sel_id,1,del_rowid);
      lrc := dbms_sql.last_row_count;
      DBMS_SQL.BIND_VARIABLE(del_id,'del_rowid',del_rowid);
      exec_del := DBMS_SQL.EXECUTE(del_id);

      -- you need to get the last_row_count earlier as it changes.
      if mod(lrc,commit_size) = 0 then
        i := i + 1;
        --dbms_output.put_line(myTable || ' Commiting Delete no ' || i || ', Rowcount : ' || lrc);
        COMMIT;
      end if;
   ELSE 
       exit;
   END IF;
 END LOOP;
  i := i + 1;
  --dbms_output.put_line(myTable || ' Final Commiting Delete no ' || i || ', Rowcount : ' || dbms_sql.last_row_count);
  COMMIT;
  DBMS_SQL.CLOSE_CURSOR(sel_id);
  DBMS_SQL.CLOSE_CURSOR(del_id);

  end_date := SYSDATE;
  e_date := TO_CHAR(end_date,'DD/MM/YY HH24:MI:SS');
  tt:= trunc((end_date - start_date) * 24 * 60 * 60,2);
  dbms_output.put_line('Deleted ' || myTable || ' Time taken is ' || tt || 's from ' || s_date || ' to ' || e_date || ' in ' || i || ' deletes and Rows = ' || dbms_sql.last_row_count);

END;
/

CREATE OR REPLACE PROCEDURE delete_test (myTable in VARCHAR2, mySql in VARCHAR2, commit_size in number) is
  i integer;
  start_date DATE;
  end_date DATE;
  s_date VARCHAR2(1000);
  e_date VARCHAR2(1000);
  tt FLOAT;
BEGIN
  start_date:= SYSDATE;
  s_date:=TO_CHAR(start_date,'DD/MM/YY HH24:MI:SS');
  i := 0;
  i := i + 1;
  dbms_output.put_line(i || ' SQL is ' || mySql);
  end_date := SYSDATE;
  e_date := TO_CHAR(end_date,'DD/MM/YY HH24:MI:SS');
  tt:= round((end_date - start_date) * 24 * 60 * 60,2);
  dbms_output.put_line(i || ' Time taken is ' || tt || 's from ' || s_date || ' to ' || e_date);
END;
/

show errors procedure delete_sql
show errors procedure delete_test

SET SERVEROUTPUT ON FORMAT WRAP SIZE 200000; 

exec delete_sql('MSF710', 'select rowid from msf710 s where  (s.equip_no, s.eq_tran_date, s.comp_data, s.rec_710_type, s.seq_710_no) not in  (select c.equip_no, c.eq_tran_date, c.comp_data, c.rec_710_type, c.seq_710_no  from  msf710_sched_comm c)', 500);






spool off;

哦,还有最后一点提示。这将是缓慢的,根据表格的不同,可能需要一些停机时间。测试、计时和调整是您在这里最好的朋友。

全部的 表中记录的,并且是 当然 删除表中所有行 命令

(在你的例子中,你只想删除一个子集,但对于任何潜伏着类似问题的人,我想我应该添加这个)

对我来说,最简单的方法是:-

DECLARE
L_exit_flag VARCHAR2(2):='N';
L_row_count NUMBER:= 0;

BEGIN
   :exit_code        :=0;
   LOOP
      DELETE table_name
       WHERE condition(s) AND ROWNUM <= 200000;
       L_row_count := L_row_count + SQL%ROWCOUNT;
       IF SQL%ROWCOUNT = 0 THEN
          COMMIT;
          :exit_code :=0;
          L_exit_flag := 'Y';
       END IF;
      COMMIT;
      IF L_exit_flag = 'Y'
      THEN
         DBMS_OUTPUT.PUT_LINE ('Finally Number of Records Deleted : '||L_row_count);
         EXIT;
      END IF;
   END LOOP;
   --DBMS_OUTPUT.PUT_LINE ('Finally Number of Records Deleted : '||L_row_count);
EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE ('Error Code: '||SQLCODE);
      DBMS_OUTPUT.PUT_LINE ('Error Message: '||SUBSTR (SQLERRM, 1, 240));
      :exit_code := 255;
END;