删除大量oracle数据方法总结.docxVIP

删除大量Oracle数据方法总结 Oracle中删除超过50w条记录的数据，如果直接使用 delete，效率就严重 受到了影响。那么首先我们需要了解对于这个表的数据，我们到底是全部删除， 还是部分删除。这里有三个关键字我们需要注意： trun cate,delete,drop 。 全部删除，不保留数据结构就直接 drop最好。如果是部分删除，一般就这 样一些办法： 1.如果删除的数据是大部分，分段提交删除的数据。 create or replace procedure delete_table is i number( 10); begi n for x in (select * from emp where DEPTNOike ‘a% ) loop delete emp where emp.id = x.id i:=i+ 1; if i 1000 then commit; i:= 0; end if; end loop; exception when others then dbms_out.put_line( sqlcode ); rollback ; end delete_table; 或者 -每500条数据提交一次 DECLARCNT NUMBER0):= 0; I NUMBER(0); BEGIN SELECTCOUNT) INTOCNT FROMjp_arrearage_bak WHERTO_CHAR(DF_DAT‘,MM )= 01; FORI IN 1..TRUNC(CNT500)+1 LOOP DELETEFROMp_arrearage_bak WHERTO_CHAR(DF_DAT‘,MM )= 01 ANDROWNUM500; COMMIT ENDLOOP; END 2、把要保留的数据放在一个临时表里,tru ncate table 原表后再放回来; from t wherecreate table t_back as select drop table t; from t where ren ame t_back to t; 3.专门使用一个大回滚段 ，比如定义：undo tablespace 2G 4、如果将方法1做一点修改，可以这么做： 有条件的分步删除数据表中的记录 -创建测试表 create table test as select * from dba_objects; -创建删除表的存储过程 create or replace procedure deleteTab -插入语句 SQl insert in to test select * from dba_objects; 6374 rows created. SQL / SQL / 6374 rows created. 6374 rows created. SQl commit; -创建删除的存储过程 create /** or replace procedure deleteTab ** Usage: run the script to create the proc deleteTab ** in SQL*PLUS, type exec deleteTab( ‘Foo，‘ID=1000000 , 3000); ** to delete the records in the table Foo, commit per 3000 records. ** Condition with default value 1=1 and default Commit batch is 10000. **/ (p_TableName in varchar2, - The TableNamewhich you want to delete from p_Condition in varchar2 default 1=1 , - Delete condition, such as id=100000 p_Count in varchar2 default 10000 - Commit after delete How many records) as pragma aut onom ous_tra nsact ion; n _delete nu mber:= 0; begi n while 1=1 loop EXECUTEMMEDIATE ‘delete from ‘ ||p_TableName|| ‘ where ||p_Condition|| ‘ and row num = :rn USINGp_C ount; if SQ!%NOTFOUNDfe n exit; else n _delete:=n_delete + SQ!%ROWCO